Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel formula help - vlookup maybe?

Posted on 2011-02-28
2
Medium Priority
?
302 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 500 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
With the emergence of Office 365 as a superior email communication platform, many organizations have started switching over to it.  After migrating to Office 365, sometimes users, as well as organizations, will have to import PST files to Office 36…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

601 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