Excel geeks, need a bit of formula help (1 Viewer)

[TxBuckeye]

I am using HLOOKUP to grab a value on another tab. The formula is pretty straightforward:

=HLOOKUP(E$6,RISK_RPT.ATAGLANCE_MTG!$A$4:$CZ$500,2,FALSE)

E$6 is a date field I use to ID the proper column. What I would like to do is grab the value one column right of this location. I tried various OFFSET and INDEX function uses but none of them seem to work. Can anyone tell me how to get the value one column to the right of the one this forumla returns? TIA.

 

[Brony]

Since you are doing an exact lookup in your HLOOKUP formula, you can substitute INDEX MATCH to get the same result.

=INDEX(RISK_RPT.ATAGLANCE_MTG!$A$4:$CZ$500,2,MATCH(E$6,RISK_RPT.ATAGLANCE_MTG!$A$4:$CZ$4,0)+1)

the above says "look at the table A4:CZ500 and get the item in the 2nd row and in the column one over from where E6 finds a match in row 4."

 

[Spin]

Since you are doing an exact lookup in your HLOOKUP formula, you can substitute INDEX MATCH to get the same result.

=INDEX(RISK_RPT.ATAGLANCE_MTG!$A$4:$CZ$500,2,MATCH(E$6,RISK_RPT.ATAGLANCE_MTG!$A$4:$CZ$4,0)+1)

the above says "look at the table A4:CZ500 and get the item in the 2nd row and in the column one over from where E6 finds a match in row 4."

This is correct.

 

[TxBuckeye]

Since you are doing an exact lookup in your HLOOKUP formula, you can substitute INDEX MATCH to get the same result.

=INDEX(RISK_RPT.ATAGLANCE_MTG!$A$4:$CZ$500,2,MATCH(E$6,RISK_RPT.ATAGLANCE_MTG!$A$4:$CZ$4,0)+1)

the above says "look at the table A4:CZ500 and get the item in the 2nd row and in the column one over from where E6 finds a match in row 4."

Thanks. This is what I was trying, but I just could not get the format down! I need to read up on this "MATCH" function.

 

[Brony]

Glad it worked. I use VLOOKUP 90% of the time, but everyone once in a while I need to look up both a row and column position and Index/Match works well here. Also note that the INDEX function can reference more than 1 cell when it does a lookup and I'll sometime use that if I want to, for example, sum up the last 3 quarters (columns) of data.

 

[TxBuckeye]

Glad it worked. I use VLOOKUP 90% of the time, but everyone once in a while I need to look up both a row and column position and Index/Match works well here. Also note that the INDEX function can reference more than 1 cell when it does a lookup and I'll sometime use that if I want to, for example, sum up the last 3 quarters (columns) of data.

I appreciate it. I was getting somewhat frustrated. You solved it in a minute.