Hi all,
I am looking for a formula which will:
- Find a value in a column.
- Then return the value in the adjacent cell to the left.
I've tried using the LOOKUP function for this. In the attachment, the formula would go in cell C4.
I'd like the formula to search E4:E13 and identify the value which matches the value in B4.
Then I'd like the value in D4 to appear in C4.
So in the attached example, inputting '2' in B4 would result in '99' in C4.
Ideas welcome, thanks.
VLOOKUP or LOOKUP cannot reference to a column in front. you would either have to switch D with E so you search in D and return E
The alternative is to use INDEX and MATCH as an array formula to get the row number, but I suggest you read up on that, you can search for that via Google.
I am currently away from my computer so cannot write the example for you.
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Just a quick one place this formula in C4
Please Login or Register to view this content.
Use VLOOKUP:
=VLOOKUP(B4,CHOOSE({1,2},$E$4:$E$13,$D$4:$D$13),2,0)
Use LOOKUP:
=LOOKUP(2,1/($E$4:$E$13=B4),$D$4:$D$13)
Or use INDEX & MATCH
=INDEX($D$4:$D$13,MATCH(B4,$E$4:$E$13,0))
Hi Keebellah and Phuocam,
Thanks for taking the time to post replies. The formulas look as though they should work but currently I am getting errors. I will try again and see if I'm missing something.
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks