match and return another cell value

**leighmills33** · 05-01-2009, 10:22 AM

Hi Guys,

I am struggling to get a result from matching a cell. I have attached an example as it is hard to explain. I want to display the result in Column H, so I need a calculation to find the value in cell F5 within column B and display the value in D if found. Column F can change value though. so my first result in cell H5 would display 0020 - D D H Double Hanging Robe.

I hope I have explained it ok.

Thanks

**NBVC** · 05-01-2009, 10:24 AM

Try:

=VLOOKUP(F5,$B$5:$D$27,3,FALSE)

copied down.

adjust ranges to suit actual data

**leighmills33** · 05-01-2009, 10:29 AM

Hi And thanks,

That works in my example but in my spreadsheet it wont work, I believe its because I have extracted column F from a row of text using this formula:

=MID(A24;SEARCH("[";A24)+1;1000)

As it was like so "Product[0020]item"

So do I have to convert this to number or can I edit the formula.

Thanks

**NBVC** · 05-01-2009, 10:33 AM

Can you post a sample that more closely reflects your actual spreadsheet?

**leighmills33** · 05-01-2009, 10:36 AM

Ok column L is where I want the information to be displayed, I want to find column K in column E and dislpay the value from in G

Thanks

**NBVC** · 05-01-2009, 10:47 AM

Your values in column E are numbers with formatting to add the leading zeros, whereas your values in column K are text strings with lead zeroes attached.... must be the same...so try:

=VLOOKUP(K8+0,$E$8:$H$268,3,FALSE)

which converts column K to number before trying to match to column E

**leighmills33** · 05-01-2009, 10:52 AM

That works great thanks.

**NBVC** · 05-01-2009, 10:53 AM

If you look at the formula in the column next to the one you put Vlookup in... you will notice the Match() function also used this concept of K8+0....