HLookup...but using the bottom row and not the top row - possible?

L

loopa1

Board Regular
Joined
Sep 3, 2006
Messages
156
Hello

I know that you can't use a HLookup to use any other row than the first as the lookup row, so is there a way you can use another excel formula (not macro) to lookup a value in the 8th row of an 8 row table (for example) and return a value from the 1st row?

Many thanks.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
I think the 'LOOKUP' formula may work here.

=LOOKUP(value, 8th row, 1st row)

HTH
 
Upvote 0
Weaver said:
I think the 'LOOKUP' formula may work here.

=LOOKUP(value, 8th row, 1st row)

HTH
Click to expand...
Provided 8th row is ordered ascending. Otherwise, something like:
=INDEX(A1:J1,MATCH(Z1,A8:J8,0))
 
Upvote 0
The INDEX formula works great...but what if I wanted to return all values in row 1 that equalled the lookup value in row 8 (Z1 in your example) and seperate them with a comma (and space after the comma) but contain them in the same cell?

For example, search for the value 8 in row 8, and it returns Name1, Name2, Name3 etc...

Thanks.
 
Upvote 0
loopa1 said:
The INDEX formula works great...but what if I wanted to return all values in row 1 that equalled the lookup value in row 8 (Z1 in your example) and seperate them with a comma (and space after the comma) but contain them in the same cell?

For example, search for the value 8 in row 8, and it returns Name1, Name2, Name3 etc...

Thanks.
Click to expand...
If they really all had to go in one cell then I would use a vba solution and not a formula.

If they could go in different cells then I would take something like Aladin has used here and modify it to work horizontally.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Formula to change font color in part of cell only
Replies
2
Views
341
Nathan Asius
N
R
  • Question
VLOOKUP formula using the TEXT function as the "lookup value"
Replies
1
Views
685
awoohaw
awoohaw
R
  • Question
Wanting to move to top and hide a row in a table once a value in column K ="CLOSED"
Replies
1
Views
211
kevin9999
K
P
  • Question
How to use a MATCH helper column containing rows from a different sheet and INDEX in an array formula to return based on data in the helper column?
Replies
0
Views
172
purekarting
P
K
  • Solved
Sumproduct with 3 tables?
Replies
4
Views
215
killerstein
K

Forum statistics

Threads
1,215,054
Messages
6,122,895
Members
449,097
Latest member
dbomb1414

Share this page

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