Help with Lookup or Index

[Frick]

Based on the number in cell A1, I need a formula that will find the matching
number in col's A5:J5 and retun the values in the matching col.

For example A1=2

A5=1 B5=2 C5=3 and so 4,5,6,7

The data section is from A6:J48
A6=3 B6=4 C6=1 and so
A7=2 B7=2 C7=5 and so on

So from this example I need a formula in L6 would return the match from the
input cell A1 with the matching col (B in this example) and return the vaule
to cell L6 which in this example is 4.

The formula would be copied down from cell L6:L48

Thanks for helping.

 

[Domenic]

Hi,

try,

=INDEX(A6:J6,MATCH($A$1,$A$5:$J$5,0))

Hope this helps!

 

[Ragdyer]

Try this:

=HLOOKUP($A$1,$A$5:$J$48,ROW()-4,0)

Drag down to copy.

 

[Vaughan]

OK

Your standing data is arranged in rows; i.e. horizontally, so you need a HLOOKUP() function.

The difficulty I have, is that below your index row (row 5) you have two rows of data. I don't know how you decide whether to return the contents of B6 or of B7.

That said, in your example, you returned the contents of B6, so lets assume you want to lookup the contents of A1 in the horizontal table indexed on A5:G5 and return the matching contents in row 6. So your HLOOKUP() function would be like this:

=HLOOKUP($A$1,$A5:$G6,2,FALSE)

$A$1 is the absolute reference of the cell containing the value you want to look up.
$A$5:$G$6 is the absolute reference of the table containg the results you want to return.

 

[Vaughan]

Sorry, a bit of premature pubblication:

.... The "2" says to return the second row of the table, and the "FALSE" to look for an exact match.

 

[Ron Rosenfeld]

Based on the number in cell A1, I need a formula that will find the matching
number in col's A5:J5 and retun the values in the matching col.

For example A1=2

A5=1 B5=2 C5=3 and so 4,5,6,7

The data section is from A6:J48
A6=3 B6=4 C6=1 and so
A7=2 B7=2 C7=5 and so on

So from this example I need a formula in L6 would return the match from the
input cell A1 with the matching col (B in this example) and return the vaule
to cell L6 which in this example is 4.

The formula would be copied down from cell L6:L48

Thanks for helping.

I'm not sure if I understand entirely what you want to do. I am assuming that
you want to return in Column L, the value of the cell whose row is the same row
as the cell in column L, and whose column number is located in A1. If that is
the case, then =INDIRECT(ADDRESS(ROW(),A1))


--ron

 

[Ragdyer]

Add an absolute for A1, and I like that better then mine!

 

[Ron Rosenfeld]

Add an absolute for A1,

Thanks for pointing that out!

=INDIRECT(ADDRESS(ROW(),$A$1))



--ron