Solved

Excel formula help - vlookup maybe?

Posted on 2011-02-28
2
275 Views
Last Modified: 2012-05-11
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
Comment
Question by:darrennelson
2 Comments
 
LVL 50

Accepted Solution

by:
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

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now