Solved Excel Formula to find if there is a match in the left two characters of cell and if so paste value below the match (1 Viewer)

[Bean Machine]

Hi All,

I am struggling with finding the best way to set up this formula in Excel. Essentially what I want to do is to check the left 2 characters in cell B28 of my worksheet titled "Data" and see if there are any matches with values in range B1:AH1 of my worksheet titled "Calculations", and, if there is a match, make the cell value equal to whatever is beneath the match found in the "Calculations" worksheet. The following are some images to hopefully help in visualizing what I am talking about doing (apologies for the funky censorship job I did).

This is in the Data worksheet. I want to pull that value of "1a" and check to see if it exists in the Calculations sheet range B1:AH1

This is in the Calculations worksheet. See that there is a match to "1a" here. Now that a match has been found in range B1:AH1 I want the value below the match, in this case "0".

Imagine that beneath the "# of Students" column the "0" value appeared after the formula did its thing. This is what I ultimately want. I hope this makes sense, if not I can clarify on anything. Thank you for your time and support I hope you all have a great day.

 

[cheekybuddha]

Try:

=HLOOKUP(Left($B$28, 2), Calculations!B1:AH2, 2, False)

 

[Bean Machine]

Try:

=HLOOKUP(Left($B$28, 2), Calculations!B1:AH2, 2, False)

This worked exactly as I wanted it to. You are an absolute life saver, thank you so much. To follow-up, lets say I wanted the next value below that cell now. How could I go about modifying the formula to do so. For example, I have four values I would ultimately like to copy into a column in my Data worksheet.

I have four values I ultimately want to copy to cells in my Data worksheet. The values in the calculations worksheet look like so:

This is what I want it to look like on my Data worksheet after the formula does its thing:

How can I go about modifying your formula to achieve this? I greatly appreciate your help and support. Thank you so much.

 

[cheekybuddha]

So, if you have in:
C31: =HLOOKUP(Left($B$28, 2), Calculations!B1:AH2, 2, False)

you can then have in the cells below:
C32: =HLOOKUP(Left($B$28, 2), Calculations!B1:AH3, 3, False)
C33: =HLOOKUP(Left($B$28, 2), Calculations!B1:AH4, 4, False)
C34: =HLOOKUP(Left($B$28, 2), Calculations!B1:AH5, 5, False)

 

[Bean Machine]

So, if you have in:
C31: =HLOOKUP(Left($B$28, 2), Calculations!B1:AH2, 2, False)

you can then have in the cells below:
C32: =HLOOKUP(Left($B$28, 2), Calculations!B1:AH3, 3, False)
C33: =HLOOKUP(Left($B$28, 2), Calculations!B1:AH4, 4, False)
C34: =HLOOKUP(Left($B$28, 2), Calculations!B1:AH5, 5, False)

This worked perfect. Thank you so much for your support. I hope you have a great day and a fantastic weekend!