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  |


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
 

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!}

Skip,

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

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!}

Skip,

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

You first need to look at Excel Help on each of these functions and begin to understand what each of them do.

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!}

Skip,

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

Maybe you could make the task easier?

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

Please post new questions in new threads.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Gavona!  Your my hero.  Great solution that totally work!