Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

remeng (TechnicalUser) (OP)
14 Feb 12 12:28
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  |

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
 
SkipVought (Programmer)
14 Feb 12 12:35


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,

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

remeng (TechnicalUser) (OP)
14 Feb 12 12:47
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
Helpful Member!  SkipVought (Programmer)
14 Feb 12 14:28

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,

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

remeng (TechnicalUser) (OP)
14 Feb 12 15:28
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
SkipVought (Programmer)
14 Feb 12 15:49
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,

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

remeng (TechnicalUser) (OP)
14 Feb 12 16:29
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
Helpful Member!  Gavona (TechnicalUser)
14 Feb 12 17:36
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

SkipVought (Programmer)
15 Feb 12 8:11



Please post new questions in new threads.

Skip,

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

remeng (TechnicalUser) (OP)
16 Feb 12 16:26
Gavona!  Your my hero.  Great solution that totally work!

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!

Back To Forum

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