Lookup entire sheet return value in column below

[aki333]

I need help to figure out how to best do this. Tried to search it and didn't see anything that works exactly. Here is what I want to do.

I want to vlookup/or hlookup into a different sheet (Bottom table is what that sheet should look like) where the value is in different column and rows and return the number underneath the lookup value.

The only way I can think of is by using combo of iferror and hlookup formula, but the formula can get very long since the actual data set I am working with is pretty large. Anyway I can do this another way?

not sure if I am explaining this correctly! Thanks for your help in advance

LookupValue	Return Value
Apple	??Formula
Plum	??Formula

<tbody>
</tbody>

Apple	Orange	Pear
10	20	30
Plum	Melon	Grape
30	40	20

<tbody>
</tbody>

 

[Eric W]

Welcome to the forum.

If the data you're looking to return is numeric, then try:

	A	B	C	D	E	F
1	Apple	Orange	Pear		LookupValue	Return Value
2	10	20	30		Apple	10
3	Plum	Melon	Grape		Plum	30
4	30	40	20

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula F2 {=IFERROR(SMALL(IF(Sheet1!$A$1:$C$3=E2,Sheet1!$A$2:$C$4),1),"No match")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If not numeric, it's tougher, but it can be done a different way.

 

[aki333]

Thank you!

 

[Eric W]

Glad to help!