PDA

View Full Version : Solved: Vlookup If Help



Emoncada
11-26-2008, 10:33 AM
I would like something like this.
I want this

If Column B = "NC8440" and Column F = "Keyboard" then Column G = "**The Formula**"

So I have a worksheet ("Spare Parts") that has column headers.
all in row 3.
So it will be something like this.
Column.............A..............B..............C............D............ .E..............F
Row3....................NC8440....................NC8500................... DC7700...
Row4...........Keyboard.......401.....Keyboard......921........Keyboard.... 192
Row5...........Touchpad......498.....Touchpad.....843........Touchpad....18 8
Row6...........Battery.........466......Battery.......882.........Display.. .....109

Basically ("Spare Parts") looks something like this.
So I need for it to look for a match in row 3 then look under that and find a match with ("Orders")Column F.

example

If ("Orders") Cell B5 = "NC8440" and Cell F5 = "Touchpad"
Then Cell G5 = "498"

If ("Orders") Cell B6 = "DC7700" and Cell F6 = "Display"
Then Cell G6 = "109"

Hope that isn't too comfusing.

Bob Phillips
11-26-2008, 11:09 AM
=INDEX(INDEX(Sheet3!A3:F6,0,MATCH(Orders!B5,3:3,0)+1),MATCH(Orders!F5,INDEX (Sheet3!A3:F6,0,MATCH(Orders!B5,3:3,0)),0))

this is an array formula

Emoncada
11-26-2008, 12:42 PM
Ok I have two Sheets.
"Orders" and "Spare Parts"

The Formula would be in the "Orders" Worksheet in Column G.

How can I make that work

Bob Phillips
11-26-2008, 12:59 PM
The bits that don't have a sheet name, precede by 'Spare Parts'!

Emoncada
11-26-2008, 01:08 PM
Ok that's where I am a little confused.
I see that you have this

MATCH(Orders!B5,3:3,0)+1)

But I need for it too look at "Spare Parts'! Row 3 then once it finds the match look under and match the next one.

Emoncada
11-26-2008, 01:23 PM
Attached is the template.
Hope that helps.

Bob Phillips
11-26-2008, 03:43 PM
As I said, all references with no sheet name should be preceded by 'Spare Parts'!, and you need to extent the range

=INDEX(INDEX('Spare Parts'!$A$3:$Z$10,0,MATCH(B3,'Spare Parts'!$3:$3,0)+1),MATCH(F3,INDEX('Spare Parts'!$A$3:$Z$10,0,MATCH(B3,'Spare Parts'!$3:$3,0)),0))

Emoncada
11-28-2008, 06:40 AM
That seems to work XLD. How can I avoid the #N/A. Do I use =ISNUMBER ? If so where would I put the ,"" ?

Bob Phillips
11-28-2008, 06:58 AM
Use 2007's new IFERROR function

=IFERROR(INDEX(INDEX('Spare Parts'!$A$3:$Z$10,0,MATCH(B3,'Spare Parts'!$3:$3,0)+1),MATCH(F3,INDEX('Spare Parts'!$A$3:$Z$10,0,MATCH(B3,'Spare Parts'!$3:$3,0)),0)),"")

Emoncada
11-28-2008, 08:01 AM
That's Perfect XLD. Thanks Again!