Need formula to return value from table based on varying order of column headers

I am setting up an invoice status spreadsheet where the data for the lookup table is copied in by the user. Depending on their source, the order of the columns may vary. The goal is for the user to get the status (e.g., Paid, Pending, Denied) based on invoice number ("Inv#"). This would be a simple lookup if I knew, for example, that invoice number would always be in column A of the data. However, that's not the case here.

So for example, they may copy data in one time as:

Date ID Name Inv# Amount Status

and the next time the order could be:

ID Inv# Status Date Amount

As a result, I don't always know which column the Invoice number or Status is in order to do a simple lookup (Note that the number of columns may vary as well, but Inv# and Status will always be one of the columns). I tried an index-match formula, but that wouldn't work for me since, again, I didn't always know which column the Invoice # would be in. I know there's a way -- just not coming to me.

Thanks in advance for your help.

Hello
Try something like the following formula:

Formula:

Please Login or Register  to view this content.

Here the 'Data_Range' is the range including headers and the 'Inv_No' is as you would expect, the Invoice number you're looking up. Assuming each Invoice Number is unique and there's a 'Status' column header somewhere in the 'Column_Headers' range, then it should return the 'Status' of the Invoice Number.

Hope this helps.
DBY

Great. Worked as described. The only thing that I noticed is that it looks like the data range has to be a named range in the Sumproduct formula.

Thanks for your help with this!

Hi
Glad it worked. The SUMPRODUCT function doesn't need a named range to work. I just did that so you could understand the formula better.

Yes. You are correct. I had mismatched data ranges in the formula so that's why I received an "N/A" answer. Thanks again for all your help.