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)

 

[Joe4]

I don't think VLOOKUP likes OR. How would it know how to handle the situation where it could match up two different values from your list?

I would recommend doing a separate VLOOKUP for each value, then Adding or Concatenating the results (depending on whether it is returning numbers or text). You will probably also have to use the ISERROR command to handle the situations in which there are no matches.

 

[IML]

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)

Is it possible for both M and SC to be in your list? If so what do you want happen? Is column F in your look tables numbers or text? If your trying to do what I think you might be, sumif may be the better tool.

 

[tobes21]

Okay, here's what I am trying to do. In column F of sheet("Cost Detail") I have a list of numbers. In column D of the same sheet I have a data validation drop down box with "L" "M" "SC" "SH" "T" "ODC" as the choices. What I want to happen is that whenever a cell has "M" "SC" "SH" or "ODC" picked from the drop down box, the number in column F appear on a different sheet in column B. I had previously had the formula on the sheet I want the number to appear on in cell B8. So I basically want the numbers to come up on the sheet ONLY when one of those specific choices is selected in column D.

 

[IML]

Okay, here's what I am trying to do. In column F of sheet("Cost Detail") I have a list of numbers. In column D of the same sheet I have a data validation drop down box with "L" "M" "SC" "SH" "T" "ODC" as the choices. What I want to happen is that whenever a cell has "M" "SC" "SH" or "ODC" picked from the drop down box, the number in column F appear on a different sheet in column B. I had previously had the formula on the sheet I want the number to appear on in cell B8. So I basically want the numbers to come up on the sheet ONLY when one of those specific choices is selected in column D.

I'm still a bit confused. If column D is all based on one validation, could you use
=IF(OR('cost detail'!$D$7={"M","SC","SH","ODC"}),SUM('cost detail'!$G$7:$G$45),"")

or if the validation are indepenant, you could try
=SUM(SUMIF('cost detail'!$D$7:$D$45,{"M","SC","SH","ODC"},'cost detail'!$F$7:$F$45))

 

[Aladin Akyurek]

Okay, here's what I am trying to do. In column F of sheet("Cost Detail") I have a list of numbers. In column D of the same sheet I have a data validation drop down box with "L" "M" "SC" "SH" "T" "ODC" as the choices. What I want to happen is that whenever a cell has "M" "SC" "SH" or "ODC" picked from the drop down box, the number in column F appear on a different sheet in column B. I had previously had the formula on the sheet I want the number to appear on in cell B8. So I basically want the numbers to come up on the sheet ONLY when one of those specific choices is selected in column D.

Perhaps...

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

 

[tobes21]

None of those seemed to work. I am not trying to sum up the numbers from column F. I just want each number that has an "M" "SC" "SH" or "ODC" selected in column D to come up in its own cell on my sheet. All of the numbers with anything other than "M" "SC" "SH" or "ODC" selected, I don't want to appear on my other sheet.

 

[Aladin Akyurek]

None of those seemed to work. I am not trying to sum up the numbers from column F. I just want each number that has an "M" "SC" "SH" or "ODC" selected in column D to come up in its own cell on my sheet. All of the numbers with anything other than "M" "SC" "SH" or "ODC" selected, I don't want to appear on my other sheet.

How did you exactly adjust what I suggested to your situation and what did you get as result?

 

[tobes21]

I entered:

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

I get "#NAME?" in the cell.

 

[Aladin Akyurek]

I entered:

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

I get "#NAME?" in the cell.

You need to replace "dropdown-cell' with a cell reference from which you select a relevant item.