IF a value is in a range, return the value in next column in same row?

[leveleyed]

Hello. I have a list using Data Validation. When the user selects an item in the list, I want the cost of the item to pop up in the next cell over. I have a list of items with prices in the next column over in the sheet. So basically, I'm looking for a formula that would do the following:

=IF(G2=(value in range B1:B10), (cost of the item in column C in same row as the item),0)

Right now my list is over 50 items, and will continue to grow, and I will have several formulas for a number of lists like this in my sheet. I am really trying to avoid typing out every single possibility as a long string of IF statements for every cell in the list.

PLEASE HELP! I'm losing my hair way too fast over this! LOL!

Thank you!
Mike

[Paul]

Hi Leveleyed, welcome to the forum.

You should be able to use a VLOOKUP formula, for example:

=VLOOKUP(G2,B1:C10,2,0)

This looks for the value from G2 in B1:B10 and returns the corresponding value from C1:C10. If it doesn't find a match, you'll see an error. To hide the error you can use:

Excel 2007 or newer:
=IFERROR(VLOOKUP(G2,B1:C10,2,0),"")

Excel 2003 or earlier:
=IF(ISERROR(VLOOKUP(G2,B1:C10,2,0)),"",VLOOKUP(G2,B1:C10,2,0))

[leveleyed]

AWESOME! Thank you so much. That's exactly what I needed.

I love your icon, BTW. Don Hertzfeldt is an awesome cartoonist.

Mike

[Paul]

THAT'S who made my icon. I saw his name once, but could never remember it!

Thanks!!