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

1. ## 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.  Register To Reply

2. ## Re: Do I use an INDEX/MATCH function or XLOOKUP for this example?

Your data is not in the greatest format, but the below should work
=INDEX(H5:K5,SUMPRODUCT((H6:K17=A3)*(COLUMN(A6:D17))))

or even
=INDEX(H5:K5,SUMPRODUCT((H6:K17=A3)*(COLUMN(A1:D1))))

it assumes your code only appears once in the range  Register To Reply

3. ## Re: Do I use an INDEX/MATCH function or XLOOKUP for this example?

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.  Register To Reply

4. ## Re: Do I use an INDEX/MATCH function or XLOOKUP for this example?

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?  Register To Reply

5. ## Re: Do I use an INDEX/MATCH function or XLOOKUP for this example?

Another option
Formula:  `Please Login or Register  to view this content.`  Register To Reply