## Excel 97 vlookup issue

## Excel 97 vlookup issue

(OP)

Hi all:

My company is still using Excel 97 (yeah I know...) and I am having a problem with a vlookup data return. Here is what is going on. When the vlookup searches the data it is stopping on the first instance of the match and not continuing until the second search is hit:

Table For search term:

| A | B | C |

3|Customer |SYSQHA | |

4|11133 |code here| |

VLOOKUP Table: raw

| B | C | D | E | F |

2|Customer |Invoice |P/N | P/N Desc. |QTY |

3|11133 |2342342 | AA | Battery | 2 |

4|11133 |2342342 |SYSQHA| Mic Sys | 2 |

=IF(VLOOKUP($A3,raw!$B:$F,3,TRUE)=$C$2,VLOOKUP($A3,raw!$B:$F,5,TRUE),"")

The vlookup finds row 3 and returns the "", but doesn't continue to row 4 as expected. Is there anyway to program it so it continues down the entire list of customers before it returns the ""?

Thank you for the help!

Mike

My company is still using Excel 97 (yeah I know...) and I am having a problem with a vlookup data return. Here is what is going on. When the vlookup searches the data it is stopping on the first instance of the match and not continuing until the second search is hit:

Table For search term:

| A | B | C |

3|Customer |SYSQHA | |

4|11133 |code here| |

VLOOKUP Table: raw

| B | C | D | E | F |

2|Customer |Invoice |P/N | P/N Desc. |QTY |

3|11133 |2342342 | AA | Battery | 2 |

4|11133 |2342342 |SYSQHA| Mic Sys | 2 |

#### CODE

=IF(VLOOKUP($A3,raw!$B:$F,3,TRUE)=$C$2,VLOOKUP($A3,raw!$B:$F,5,TRUE),"")

The vlookup finds row 3 and returns the "", but doesn't continue to row 4 as expected. Is there anyway to program it so it continues down the entire list of customers before it returns the ""?

Thank you for the help!

Mike

## RE: Excel 97 vlookup issue

hi,

that's ALL you get with a lookup.

Please explain WHAT you want to report, rather than HOW you think it ought to be done.

In other words, display the RESULT you want based on the example you posted.

Skip,

_{Just traded in my old subtlety...for a NUANCE!}## RE: Excel 97 vlookup issue

Good to see you as always. I'd like it to return the

F4 result that = 2 since D4 = the search term SYSQHA

| B | C | D | E | F |

4|11133 |2342342 |SYSQHA| Mic Sys | 2 |

Thanks,

Mike

## RE: Excel 97 vlookup issue

output: in A1...

3 4

Customer SYSQHA

11133 Mic Sys 2

the formula using NAMED RANGES...

B3: =INDEX(OFFSET(Customer,MATCH($A3,Customer,0)-1,B$1,COUNTIF(Customer,$A3),1),MATCH($B$2,OFFSET(Customer,MATCH($A3,Customer,0)-1,2,COUNTIF(Customer,$A3),1),0),1)

COPY b3 to c3

Skip,

_{Just traded in my old subtlety...for a NUANCE!}## RE: Excel 97 vlookup issue

Thank you for working out the code for me. Since I want to learn this so I don't have to ask the same question again, could you please walk me through the formula so I can completely understand what it is doing? I just want to guess at it for the future.

Thanks allot for the support!

Mike

## RE: Excel 97 vlookup issue

INDEX()

MATCH()

COUNTIF()

OFFSET()

To summarize,

INDEX() returns a value, using a RANGE reference and a ROW & COLUMN offset

MATCH() returns an offset, using a lookup value in a range for an EXACT or Greater Than of Less Than value in the range

COUNTIF() returns a count of the number of occurrences of a value in a range

OFFSET() returns a RANGE

=INDEX(OFFSET(Customer,MATCH($A3,Customer,0)-1,B$1,COUNTIF(Customer,$A3),1),MATCH($B$2,OFFSET(Customer,MATCH($A3,Customer,0)-1,2,COUNTIF(Customer,$A3),1),0),1)

Customer is a Named RANGE.

$B$2 is an ABSOLUTE reference to the cell where you entered SYSQHA.

B$1 is a mixed reference to the column offsets in row 1 that point to the proper column in your source table: col 3 & 4.

Skip,

_{Just traded in my old subtlety...for a NUANCE!}## RE: Excel 97 vlookup issue

I will look into the functions (97 just sucks lol on this stuff). If I wanted to define a series of part numbers from multiple cells and want to return a 1 if any of them are valid, or a 0 if the statement is false in the previous formula:

| A | B | C | D

1 |Part # | bob | frank | sam

bob is there = 1

none are there = 0

how can I go about doing it?

Thank you!

Mike

## RE: Excel 97 vlookup issue

For the first problem you could add a column to be the first column of the RAW table, It would become column B. This column would contain a concatenation of Customer & P/no, for example;

=C1&E1

Then your lookup could be =vlookup($A3&$C$2,raw!$B:$G,6,FALSE)

NB I am pretty sure you want an exact match rather than the nearest match so the last parameter should be FALSE not TRUE.

I think that helps you to address your most recent question too.

Gavin

## RE: Excel 97 vlookup issue

Please post new questions in new threads.

Skip,

_{Just traded in my old subtlety...for a NUANCE!}## RE: Excel 97 vlookup issue