Xlookup Across Multiple Columns

[mvbernot]

I generated a formula to "spill" the 20 largest OT $ that exist across multiple pay periods (columns B to E). I have those 20 items listed in one column. I want to be able to able to return the address for those large OT $ items (those address exist in Column A). I can't seem to get a formula to return the address. Why would =Xmatch("OT amount",B3#) return a #VALUE error (where B3# is the spilled pay periods - columns B to E). I keep getting Xlookup errors that I can seem to evaluate to understand what I'm doing wrong. I thought this would be an easy formula, but I'm missing some key understanding of the new function Xlookup. Thank you.

 

[CA_Punit]

I can't seem to get a formula to return the address.

Do you want to return the cell address ?? I would immensely help if you could paste a data using xl2bb

 

[jasonb75]

Could you post a fictionalised sample of your data with the expected results using XL2BB (see link below).

Approx 10 rows of data with an expected output for say top 3 should be sufficient to demonstrate exactly what is needed.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

I haven't used the new functions much (have them at work but not at home yet), but to me your formula of =Xmatch("OT amount",B3#) looks no different to =MATCH("OT amount",B3,0) in that it is looking for an exact text string in a single cell.

 

[C Moore]

Not 100% sure I follow. XMATCH is one dimensional and it sounds like you want to find it in two dimensions. The following has a row and column formula you can adjust to make an address. (Conditional formatting of top 20 was just for my visual sake).

											LARGE	ROW	COLUMN
	849.6288	512.0024	993.5241	776.4193		849.6288	512.0024	993.5241	776.4193		993.5241	2	9
	476.8246	947.38	149.8932	166.7215		476.8246	947.38	149.8932	166.7215		968.5586	10	9
	223.3656	794.8143	673.0806	444.7785		223.3656	794.8143	673.0806	444.7785		953.3834	14	10
	388.5469	756.8869	295.2203	149.5972		388.5469	756.8869	295.2203	149.5972		953.0266	15	10
	77.46947	785.9986	34.98053	540.9872		77.46947	785.9986	34.98053	540.9872		947.38	3	8
	741.6924	770.3657	597.8585	292.4853		741.6924	770.3657	597.8585	292.4853		937.2144	15	9
	367.9682	153.366	913.0734	658.6103		367.9682	153.366	913.0734	658.6103		932.9233	12	9
	801.5802	591.9997	878.8913	65.73112		801.5802	591.9997	878.8913	65.73112		913.0734	8	9
	251.9119	162.651	968.5586	603.9387		251.9119	162.651	968.5586	603.9387		878.8913	9	9
	270.1723	497.4243	424.5297	368.8756		270.1723	497.4243	424.5297	368.8756		870.179	13	10
	365.917	513.36	932.9233	349.7355		365.917	513.36	932.9233	349.7355		868.7125	14	8
	86.27719	397.1984	836.5015	870.179		86.27719	397.1984	836.5015	870.179		849.6288	2	7
	691.2038	868.7125	42.47865	953.3834		691.2038	868.7125	42.47865	953.3834		836.5015	13	9
	110.4356	751.9612	937.2144	953.0266		110.4356	751.9612	937.2144	953.0266		801.5802	9	7
	605.1721	259.0796	52.94331	30.19553		605.1721	259.0796	52.94331	30.19553		794.8143	4	8
	Data					Spilled Data					Large
						=B2:E16					=LARGE($G$2#,ROWS($M$2:M2))
											ROW
											=SUMPRODUCT(($G$2#=M2)*ROW($G$2#))
											COLUMN
											=SUMPRODUCT(($G$2#=M2)*COLUMN($G$2#))

 

[mvbernot]

Address Paydate1 Paydate2 Paydate3
Site1 500

 

[mvbernot]

Thank you for your responses. I will try to get the Add In loaded on my laptop so I can share the file. I apologize for not being able to clearly identify the problem I'm having. C Moore - I think I can use your "ROW" formula to get the information I'm trying to return. I need to devote some more time in learning the new functions so I can understand what the errors are telling me.

 

[jasonb75]

so I can understand what the errors are telling me

#VALUE! means that you have tried to do something with text that can only be done with a number, for example ="fred"+1
A MATCH or LOOKUP type function that can't find a search result usually returns #N/A!
Although possible, I think it highly unlikely that the error types shown have been changed with the new functions.

As I pointed out earlier, I don't have the new functions on my personal laptop to test anything, I'm only here posting observations, I'll leave the rest to the others.