Xlookup and Match

[Peter Davison]

I have tried to use Xlookup which is working okay when the column to search is not variable.
For example I want to look up the Rank number 1 but it could be in any one of 10 columns so I want to match which column to use.

This is my xlookup formula -
XLOOKUP($B187,'Pivot Vol'!$AE$9:$AE$5000,'Pivot Vol'!$B$9:$B$5000,"",0)
I want to be able to use match for the AE column because it could be any column AB to AG.
The return column will always be B.
B187 is a number it is looking for in the search column

So its like I normally use a VLookup with Match but want to use XLookup and match but can't work it out.

Could anyone help me. It would be really appreciated?

Thanks

 

[RoryA]

What are you trying to match to find which column to use?

 

[Peter Davison]

I have 11 columns to search and each column has a store number at the top of the column so I want to match the column by this?
Hope that is clear

 

[RoryA]

Where is the store number held?

Basically you want:

XLOOKUP($B187,INDEX('Pivot Vol'!$AB$9:$AG$5000,0,MATCH(A2,'Pivot Vol'!$AB$8:$AG$8,0)),'Pivot Vol'!$B$9:$B$5000,"",0)

where A2 is the store number cell.

 

[Peter Davison]

Brilliant, that is most helpful.
Appreciate your time.

I have a separate question shortly trying to do a dynamic range for a chart using Offset and Counta by row. I will come back to you.
Thanks again

 

[Peter Davison]

I believe I've cracked the problem above.
I think it is because I needed the following
=OFFSET(Dashboard!$FA$6,0,1,COUNTA(Dashboard!$6:$6))
and I had blank columns between series's.

Thanks again for all your help.