Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Excel 97 vlookup issue

Excel 97 vlookup issue

Excel 97 vlookup issue

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!


RE: Excel 97 vlookup issue


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.


glassesJust traded in my old subtlety...
for a NUANCE!tongue

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  |



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


glassesJust traded in my old subtlety...
for a NUANCE!tongue

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!


RE: Excel 97 vlookup issue

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



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


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.


glassesJust traded in my old subtlety...
for a NUANCE!tongue

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!


RE: Excel 97 vlookup issue

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;

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.


RE: Excel 97 vlookup issue

Please post new questions in new threads.


glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Excel 97 vlookup issue

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close