Lookup Function

sdchris

New Member
Joined
Oct 28, 2011
Messages
29
Hello,

I would like to write a formula to return a value from a coulmn that relates to an array within which my lookup value exists. Confused? See the tables below:


IDV1V2V3
105273149
1075968130
10912491682
1111931
112731116

<tbody>
</tbody>


RankValueID
112491
2596
3149
481
573

<tbody>
</tbody>


The tables above are on different sheets within a workbook. I would like to write a formula that returns the "ID" numbers from column "A" in the first table, based on values in columns B-D in that first table, into column "C" in the second table. For example, in this case, the ID number that corresponds with the value "12491" would be "109", since 12491 corresponds with 109 in the first table.

FYI, the "Value" numbers in the second table are calculated based on their rank (high-low) within the matrix in the first table.

Please let me know if you have suggestions. I tried the INDEX-MATCH function, but it doesn't seem to work if I'm trying to find a value within a 2-D array- it only works if I'm looking in a single column.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
The VLOOKUP fcn is probably your best bet here, however you will have to put copies of the ID values in a column after your data. (eg if the ID is in column A, B-D = values then you need to put copies of the ID values after column D).

Lets say for example that you use column E, the formula =A2 would copy the first ID of 105. drag the formula down sothat all ID have bee cfwd.

In the second sheet in cell C2 use this formula to lookup the ID:

=VLOOKUP(B2,'Sheet1'!$A$2:$E$6,5,false)

so this is telling you to lookup the value at cell B2 on the current sheet on Sheet1 (or whatever your have called it) within the A2 to E6 range of cells and that it to show the value of the 5th column within that range. The false at the end tells it to look for an exact match.

Hope this helps.

Woody
 
Last edited:
Upvote 0
Try

=INDEX(A1:A10,MATCH(C1,B1:B10,0))

Where A1:A10 houses ID
Where C1 is your search criteria
Where B1:B10 houses values
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top