Is there a way to use HLOOKUP to return a value derived by a formula?

**mozartk626** · 08-05-2022, 02:55 PM

I have an array with names in the headings that are derived from a separate array on a different worksheet. I'm using an HLOOKUP to find my name in the headings and return a numeric result from the column underneath my name. The problem is, my name in the array is derived from a formula, so the HLOOKUP is not finding it. How would I modify my HLOOKUP formula?

- In the attached example, my first worksheet is called "Pre-Draft." F15 contains my name, and it is hard coded as text, so no problem there.

- My second sheet is called "Roster Picks." My name is found in H1. This is NOT a text cell. My formula retrieves my name from an array on the "Pre-Draft" worksheet, and must remain as a formula since the order of the names in the Pre-Draft array can change:

=IF('Pre-Draft'!$F5="","",'Pre-Draft'!$F5)

If I use an HLOOKUP to find my name and return a cell reference, the formula does not find my name because it comes from a formula. How do I get around this? My hlookup is in cell C12, and returns the wrong number because it is not finding my name. The correct result should be "29", not "5".