Solved

# Excel formula help - vlookup maybe?

Posted on 2011-02-28
275 Views
not sure if vlookup is what i want here, but here's what i need:

A    B    C    D
5
6          1
7                 2
8     3
9                 4

i need a formula that will look at a value (X) and compare to table above.  If X is in column A, set = X.  If X is in column B, C, or D, set = to the corresponding value in column A.  So, if X=2, set = 7 or if X=5, set = 5.
0
Question by:darrennelson

LVL 50

Accepted Solution

barry houdini earned 125 total points
ID: 35001938
Hello darren,

If your table is in A1:D5 and the "lookup value" is in A8 then you can use this "array formula"

=INDEX(A\$1:A\$5,SMALL(IF(A\$1:D\$5=A8,ROW(A\$1:D\$5)-ROW(A\$1)+1),1))

confirmed with CTRL+SHIFT+ENTER

If you want to avoid error values if the lookup value isn't in the table then switch to a version like this

=IF(COUNTIF(A\$1:D\$5,A8),INDEX(A\$1:A\$5,SMALL(IF(A\$1:D\$5=A8,ROW(A\$1:D\$5)-ROW(A\$1)+1),1)),"No Match")

That will return "No match" if lookup value doesn't exist

see attached

regards, barry
26853833.xls
0

Author Closing Comment

ID: 35002683
exactly what i was looking for, thanks
0

## Featured Post

### Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.