Do I use an INDEX/MATCH function or XLOOKUP for this example?

I have attached a simple template showing what I want to achieve, I have tried various functions to get the desired result.

Essentially, the returned value should be £27,000 by using the criteria in cell A3, finding it in the range H6:K17 and then returning the value in the range H5:K5.

Any help would be most appreciated.

Thank you, the 2nd formula works on the example but it isn't working when I transfer to the main workbook that has more data. What does the COLUMN(A1:D1) actually do as the cells are blank? This might be why it isn't working in my main schedule.

All you need is which column in the range h5:k5 is the the match in

the sumproduct formula will return a value of 1 to 4 column A is 1, B 2 etc


then you are just indexing this value form the range h5:k5

eg if the match was in column J, it should return a 3 as it is the value of column C, the third value in the range h5:k5 is j5

H:k has to be as wide as A:D is this true?

Another option

Formula:

Please Login or Register  to view this content.