Xlookup Across Multiple Columns

mvbernot

New Member
Joined
Nov 25, 2006
Messages
13
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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows
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.


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

Well-known Member
Joined
Jan 17, 2014
Messages
544
Office Version
  1. 365
Platform
  1. Windows
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).
LARGEROWCOLUMN
849.6288512.0024993.5241776.4193849.6288512.0024993.5241776.4193993.524129
476.8246947.38149.8932166.7215476.8246947.38149.8932166.7215968.5586109
223.3656794.8143673.0806444.7785223.3656794.8143673.0806444.7785953.38341410
388.5469756.8869295.2203149.5972388.5469756.8869295.2203149.5972953.02661510
77.46947785.998634.98053540.987277.46947785.998634.98053540.9872947.3838
741.6924770.3657597.8585292.4853741.6924770.3657597.8585292.4853937.2144159
367.9682153.366913.0734658.6103367.9682153.366913.0734658.6103932.9233129
801.5802591.9997878.891365.73112801.5802591.9997878.891365.73112913.073489
251.9119162.651968.5586603.9387251.9119162.651968.5586603.9387878.891399
270.1723497.4243424.5297368.8756270.1723497.4243424.5297368.8756870.1791310
365.917513.36932.9233349.7355365.917513.36932.9233349.7355868.7125148
86.27719397.1984836.5015870.17986.27719397.1984836.5015870.179849.628827
691.2038868.712542.47865953.3834691.2038868.712542.47865953.3834836.5015139
110.4356751.9612937.2144953.0266110.4356751.9612937.2144953.0266801.580297
605.1721259.079652.9433130.19553605.1721259.079652.9433130.19553794.814348
DataSpilled DataLarge
=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

New Member
Joined
Nov 25, 2006
Messages
13

ADVERTISEMENT

Address Paydate1 Paydate2 Paydate3
Site1 500
 

mvbernot

New Member
Joined
Nov 25, 2006
Messages
13
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows
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 :oops:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top