Using OR function with VLOOKUP

[tobes21]

I was wondering if it is possible to do a VLOOKUP where the lookup_value can be more than one value. I tried using an OR function but that didnt seem to work. I would like the VLOOKUP function to work for any of the values of text "M", "SC", "SH", "ODC". Here is my formula:

=VLOOKUP(OR("M","SC","SH","ODC"),'Cost Detail'!$D6:$G45,3,FALSE)

 

[Juan Pablo González]

You're supposed to change this:

'Cost Detail'!dropdown-cell

with the actual reference of that cell.

 

[tobes21]

I switched the formula to:

=IF(OR('Cost Detail'!D6={"M","SC","SH","ODC"}),OR('Cost Detail'!$F6:$F45,OR('Cost Detail'!D6)),"")

And now I get #VALUE!

 

[Aladin Akyurek]

I switched the formula to:

=IF(OR('Cost Detail'!D6={"M","SC","SH","ODC"}),OR('Cost Detail'!$F6:$F45,OR('Cost Detail'!D6)),"")

And now I get #VALUE!

Why don't you try...

=IF(OR('Cost Detail'!$D6={"M","SC","SH","ODC"}),INDEX('Cost Detail! $G1 :$G45,ROW('Cost Detail'!$D6)),"")

If you want return from the F-range in Cost Detail...

=IF(OR('Cost Detail'!$D6={"M","SC","SH","ODC"}),INDEX('Cost Detail! $F1 :$F45,ROW('Cost Detail'!$D6)),"")

and report back what you get?

 

[tobes21]

It works exactly the way I want it to. Thanks a lot.

 

[tobes21]

One more quick question if I may. Suppose I want to have the same exact thing happen, but only for one selection, specifically "T". instead of 4 different ones. So if "T" is selected in the drop down box, I want the number in row F to appear on my sheet.

 

[Aladin Akyurek]

One more quick question if I may. Suppose I want to have the same exact thing happen, but only for one selection, specifically "T". instead of 4 different ones. So if "T" is selected in the drop down box, I want the number in row F to appear on my sheet.

That would be simply...

=INDEX('Cost Detail! $F1 :$F45,ROW('Cost Detail'!$D6))

with D6 housing "T".

Or, if you want to make sure that it is "T" that is looked up...

=IF('Cost Detail'!$D6="T",INDEX('Cost Detail! $F1 :$F45,ROW('Cost Detail'!$D6)),"")

 

[tobes21]

It works, but it only seems to work for the first row that I apply it to. It turns out that the same things happens for the other one with the 4 different choices. Say in D6 of 'Cost Detail' I have "T" and in F6 I have "1.1", it puts "1.1" on the correct sheet just fine. Also if I then go to D7 and choose "SC" and "1.2" in F7 that works fine too. But say I go to D8 and choose "T" and 1.2 it gives me a "-" on the appropriate cell in the sheet it is supposed to go into.

 

[Aladin Akyurek]

It works, but it only seems to work for the first row that I apply it to. It turns out that the same things happens for the other one with the 4 different choices. Say in D6 of 'Cost Detail' I have "T" and in F6 I have "1.1", it puts "1.1" on the correct sheet just fine. Also if I then go to D7 and choose "SC" and "1.2" in F7 that works fine too. But say I go to D8 and choose "T" and 1.2 it gives me a "-" on the appropriate cell in the sheet it is supposed to go into.

Care to post 10 rows from Cost Detail?

 

[tobes21]

Sure, how do I post the actual excel thing though?

 

[Aladin Akyurek]

Sure, how do I post the actual excel thing though?

Try using HtmlMaker from:

http://www.interq.or.jp/sun/puremis/colo/HtmlMaker.htm