Reverse HLOOKUP with a range

Question

Reverse HLOOKUP with a range

Dany Wagner 0

Hi! Please help!

Any way to make a "reverse hlookup" function to get value from the 1st row knowing the date? (see the pic)

Thanks in advance!

AllenXu-MSFT 24,991 Reputation points Moderator

2023-06-05T06:40:42.2733333+00:00

@Dany Wagner ,

Welcome to Microsoft Q&A.

I cannot get you clearly as per the description. For help you better, could you please share an example here? What exactly the function of "reverse Hlookup" should be? From the screenshot below, you want to get the value from 1st row C1 "2-JUN-2023" to C14 using the function, right?
Dany Wagner 0 Reputation points

2023-06-05T08:06:12.25+00:00

@AllenXu-MSFT Hi! Thanks for your reply! Yes. Having a date I would like to know the shift. Is the date from column day, night or day-off.
So I believe it should be a Hlookup but it does not work down to up

3 answers

Your answer

AllenXu-MSFT 24,991 Reputation points Moderator

2023-06-05T06:40:42.2733333+00:00

@Dany Wagner ,

Welcome to Microsoft Q&A.

I cannot get you clearly as per the description. For help you better, could you please share an example here? What exactly the function of "reverse Hlookup" should be? From the screenshot below, you want to get the value from 1st row C1 "2-JUN-2023" to C14 using the function, right?
Dany Wagner 0 Reputation points

2023-06-05T08:06:12.25+00:00

@AllenXu-MSFT Hi! Thanks for your reply! Yes. Having a date I would like to know the shift. Is the date from column day, night or day-off.
So I believe it should be a Hlookup but it does not work down to up

Answer 1

IlirU 2,571 Volunteer Moderator

User's image

Hi,

If you do not have the BYCOL or FILTER functions in your version of Excel, then use the formula below instead. Apply it in cell C14 (see the screenshot above).

=INDEX(A1:D1,, SUM(IFERROR(COLUMN(A2:D11) / MMULT(TRANSPOSE(ROW(A2:D11)^0), --(A2:D11 = B14)), 0)))

For Excel 2016 or earlier versions, this formula must be entered as an array formula, which means you need to press CTRL + SHIFT + ENTER simultaneously on your keyboard.

HTH

0 comments

Answer 2

Ashish Mathur 102.3K Volunteer Moderator

Hi,

In cell C14, enter this formula

=FILTER(A1:D1,BYCOL(A2:D11=B14,OR))

Hope this helps.

User's image

0 comments

Answer 3

Dany Wagner 0

Screenshot 2023-06-04 at 00.22.20

0 comments