Lookup Function

[sdchris]

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:


[TABLE="width: 276"]
<tbody>[TR]
[TD]ID[/TD]
[TD]V1[/TD]
[TD]V2[/TD]
[TD]V3[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]27[/TD]
[TD]3[/TD]
[TD]149[/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD]596[/TD]
[TD]81[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]109[/TD]
[TD]12491[/TD]
[TD]68[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]19[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]112[/TD]
[TD]73[/TD]
[TD]11[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 207"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Value[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12491[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]596[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]149[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]81[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]73[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[Woody75]

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

 

[Brian from Maui]

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