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.

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

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.

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?

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.`

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1