Multiple vlookup values based on text in another cell

[jmewebb]

Hi,

I am trying to perform a vlookup based on text in another cell. Depending on the text in that cell, the vlookup needs to use one of two lookup values.

Please see attached example.

HELPsample.xlsx

Thank you in advance for your assistance.

[rcm]

for line 2 this would return the desired result

Please Login or Register  to view this content.

[bebo021999]

If there are only A,B,C type, use rcm's.
If there are others D,E,F...
=IF(D2="A",VLOOKUP(F2,Sheet2!$A$2:$F$4,6,0),IF(OR(D2="B",D2="C"),VLOOKUP(Sheet1!G2,Sheet2!$B$2:$F$4,5,0),""))

[etaf]

you could just put an IF in place

=IF( D2="A", vlookup , IF ( OR( D2="B", D2="C") , otherVlookup, "D is not A,B,C" ))

not sure how your vlookup would work

can you describe the vlookups

you may need to use index/match

[jmewebb]

This worked! However, it is returning the numbers as negatives. Any idea why this is happening?

This is the formula in my spreadsheet:

=-IF(P92="Clarity ID",VLOOKUP(AK92,'Proj FYTD'!A:J,10,0),IF(OR(P92="NPW",P92="Service Desk"),VLOOKUP('ER Assignments'!AX92,'Proj FYTD'!B:J,9,0),""""))

Also, is there a way to return "0" instead of "#N/A"?

THANK YOU!!!!!!

[etaf]

can you post the spreadsheet ?
would only be negative if the values on column 10, or 9 are negative

[Tony Valko]

This worked! However, it is returning the numbers as negatives. Any idea why this is happening?

This is the formula in my spreadsheet:

=-IF(P92="Clarity ID",VLOOKUP(AK92,'Proj FYTD'!A:J,10,0),IF(OR(P92="NPW",P92="Service Desk"),VLOOKUP('ER Assignments'!AX92,'Proj FYTD'!B:J,9,0),""""))

Also, is there a way to return "0" instead of "#N/A"?

It may be returning a negative value because you're negating the result by including the minus sign at the beginning of the formula.

See if this corrects that:

=IF(P92="Clarity ID",VLOOKUP(AK92,'Proj FYTD'!A:J,10,0),IF(OR(P92={"NPW","Service Desk"}),VLOOKUP('ER Assignments'!AX92,'Proj FYTD'!B:J,9,0),""))

To trap the #N/A error:

=IFERROR(IF(P92="Clarity ID",VLOOKUP(AK92,'Proj FYTD'!A:J,10,0),IF(OR(P92={"NPW","Service Desk"}),VLOOKUP('ER Assignments'!AX92,'Proj FYTD'!B:J,9,0),"")),0)

[jmewebb]

Thank you, Tony! This worked!

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been answered please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools>Mark this thread as solved.