Forum Discussion
XLOOKUP search w/multiple output
I've been working on a project and I am completely stumped. I cannot wrap my brain around how to get this to work. In the example below, I want to Enter a number in either of the top two field and have the last three fields populate.
Now, I can get this to work for one, but not the other and for some reason when I switch from bottom search to the top search the old data doesn't clear and the search doesn't work.
This is the formula I have so far //=XLOOKUP(H8,A2:A10606,B2:B10606)
Anything I have done after has been a disaster
A macro/VBA routine probably could be used to place data in the white fields. Formulas put data in the space with the formula. Just as yours does now. But once a formula has been written, it's good to go. I personally don't like to use (and don't) macros, preferring by far to write formulas.
Without knowing how this is going to be used over the longer term, I'd revise it slightly to make clear that data entry is only in the yellow background cells (a useful convention to adopt). Right now it's set up so you can enter things in either or both. You may run into problems from time to time as it appears that some of your numbers are entered as numbers, others as text. To be consistent, given the variety of formats of D365 numbers, you should keep them all as txt.
8 Replies
Much the same as NikolinoDE
=IF(ISTEXT(OnChNum), "("&OnChNum&")", XLOOKUP(part, oldNumber, OnChList)) =IF( ISTEXT(OnChNum), XLOOKUP(OnChNum, OnChList, description), XLOOKUP(part, oldNumber, description) )The only other thing I would suggest is that if both the OnCh and part numbers are present you use conditional formatting to hide the part number.
The conditional format would test the OnCh entry but apply number formatting of the form "---" to the part number entry.
Hi Peter, you might consider taking another look at how ISTEXT() behaves within your formula.
ISTEXT() checks specifically whether a cell contains text — it doesn’t evaluate whether the cell contains a valid lookup value. If someone enters a numeric OnCh number (which yours appear to be), ISTEXT() will return FALSE, even though the entry itself is entirely valid.
For example, if OnChNum = 1010 (a number), ISTEXT(OnChNum) evaluates to FALSE. In that case, the formula would not continue with the lookup, even though a correct value has been provided.
It may therefore be helpful to base the condition on whether a value is present rather than whether it is text. This keeps your overall logic intact while making the behavior more flexible for different input types.
True. I started with NOT(ISBLANK(...)) and was over-hasty with the refactoring!
As an additional option,
=LET(
lookupValue, IF(H9<>"", H9, H7),
lookupColumn, IF(H9<>"", A2:A10606, C2:C10606),
XLOOKUP(lookupValue, lookupColumn, B2:B10606, ""))Hello,
I just saw that it was up today. I was unable to find a way to edit it after I hit submit. My apologies for not being clear.
I would like to be able to enter a number in one of the top two fields in the blue form and have the results populate the bottom three boxes.
//=XLOOKUP(H8,A2:A10606,B2:B10606)
the above formula is/was in the field next to D365 Part#.
It is dummy data at the moment only because I don't want to post the real data just in case it is proprietary. The real data will be setup exactly the same.
The only thing that has gotten me close to my goal is having some sort of XLOOKUP in every "white" field in the blue form. I know there has got to be a better way.
Thank you for any assistance/insight.
https://docs.google.com/file/d/1VmFb2bHvvyGhXktnsOfWiJ0S-bo0_tVX/edit?usp=docslist_api&filetype=msexcel
A macro/VBA routine probably could be used to place data in the white fields. Formulas put data in the space with the formula. Just as yours does now. But once a formula has been written, it's good to go. I personally don't like to use (and don't) macros, preferring by far to write formulas.
Without knowing how this is going to be used over the longer term, I'd revise it slightly to make clear that data entry is only in the yellow background cells (a useful convention to adopt). Right now it's set up so you can enter things in either or both. You may run into problems from time to time as it appears that some of your numbers are entered as numbers, others as text. To be consistent, given the variety of formats of D365 numbers, you should keep them all as txt.
You put this query up three days ago, and have had zero responses. I am going to offer an explanation: your query doesn't make sense. Not fully. It's clear that you want to be able to enter one of two data items (at the top of that blue box) and have the rest of them filled in by Excel. That IS clear. What's not at all clear is where the formula //=XLOOKUP(H8,A2:A10606,B2:B10606) fits in all of it. What cell, for example, is H8? Is it one of the ones showing in the blue box area? And where in heaven's name do you have the formula itself in all of this?
Finally, instead of images, please post the actual file. From all appearances, it's dummy data already; if not, make it anonymous so that no proprietary information is revealed. If you can't post it here in the forum, use OneDrive or Google or the equivalent and paste a link here.
