HLOOKUP in Excel Formula

0 votes

This is my excel file:

enter image description here

In this Excel document, cell J2 needs to contain a formula that does the following: In the filled matrix, it would search for the letter C (column), and then it would give me the value. For instance, if J2 had the number 43,24 as its first value, I would drag the formula all the way to the end (the 14th row, where the matrix's filled values are fixed $$).

The Final output should be like this:

enter image description here

My problem is that I can't fix the filled matrix ($$) in my HLOOKUP/VLOOKUP.

Can someone please help me with this?

Dec 17, 2022 in Others by Kithuzzz
• 28,700 points
63 views

1 answer to this question.

0 votes

If you want to switch up the C and F, you need one INDEX with a MATCH for the row and a MATCH for the column, like this: 

=INDEX($B$2:$G$14,MATCH($I2,$A$2:$A$14,0),MATCH(J$1,$B$1:$G$1,0))

enter image description here

If you don't need to do a match on the date, then simply remove that part of the formula:

=INDEX($B$2:$G$14,,MATCH(J$1,$B$1:$G$1,0))

Or if you want to use HLOOKUP, then like this:

=HLOOKUP(J$1,$B$1:$G$14,ROW(),TRUE)

enter image description here

answered Dec 17, 2022 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer

Moving Average formula in Excel not autofilling in table

You could use AVERAGEIFS to make the ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 53,160 points
138 views
0 votes
1 answer

INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX($I$2:$I$26,MATCH(V4,$R$2:$R$26,0))) The calculation ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 53,160 points
159 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 53,160 points
201 views
0 votes
1 answer

How to get sum of all matches of HLOOKUP in Excel?

Consider: =SUMPRODUCT((A1:E1="apple")*(A2:E2)) To include more ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 53,160 points
233 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
213 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
386 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,630 points
175 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 22,940 points
162 views
0 votes
1 answer
0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 53,160 points
97 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP