Using XLOOKUP for 2 search criteria

**santoma** · 01-13-2023, 01:05 PM

Hi!

I need to find a way to use the Xlookup function to be able to find an specific cell. In the example provided, I need the XLOOKUP to match BOTH the patient initials and the Store number (because there might be several same stores with different initials, or same initials in different stores) in order to provide the correct return array. I have tried nesting one XLOOKUP inside another one, or also if statements, but cannot manage it to work, any ideas?

I want the Return array to be C:C "the issue" column, while having to search and match A:A & B:B (initials and store)

Annotation 2023-01-13 120712.png

Example xlookup.xlsx

**Pete_UK** · 01-13-2023, 01:23 PM

In your example file you have two as values in column A with the same store number in B - what do you want to see as the result?

Pete

**santoma** · 01-13-2023, 01:42 PM

So sorry I will edit post to reflect my need. I want the Return array to be C:C "the issue" column, while having to search and match A:A & B:B (initials and store)

**sandy666** · 01-13-2023, 01:50 PM

something like this ?

Patient initials	Store	Indicate the issue
as	1541	No demand
as	1541	Barcode does not scan

different indicate issue

**Fluff13** · 01-13-2023, 02:18 PM

How about

Formula:

Please Login or Register  to view this content.

**santoma** · 01-13-2023, 02:44 PM

Not exactly like that. As I mentioned in my post. There might be same initials and different stores or viceversa. I need the function to match both of those (A:A & B:B) and return C:C, even if there are duplicates of just A:A or B:B. Basically a search bar

**sandy666** · 01-13-2023, 02:46 PM

to whom is your last post?

another option

Patient initials	Store	Indicate the issue
as	1541	No demand, Barcode does not scan

you can choose between post#4

Please Login or Register  to view this content.

or
post#7

Please Login or Register  to view this content.

**Fluff13** · 01-13-2023, 02:53 PM

I need the function to match both of those (A:A & B:B) and return C:C

That's exactly what the formula I supplied does.

**CARACALLA** · 01-13-2023, 02:55 PM

G8=TEXTJOIN("-",TRUE,IF((Table2[Patient initials]=$E8)*(Table2[Store]=$F8),Table2[Indicate the issue],""))

**santoma** · 01-13-2023, 03:09 PM

Originally Posted by Fluff13

How about

Formula:

Please Login or Register  to view this content.

This works! although it "Spills" if I have multiple same initials (which will possibly happen) but this solves my issue, appreciate it!

**Fluff13** · 01-13-2023, 03:12 PM

Glad to help & thanks for the feedback.

Using XLOOKUP for 2 search criteria

LinkBack

Thread Tools

Rate This Thread

Display

Using XLOOKUP for 2 search criteria

Re: Using XLOOKUP for 2 search criteria

Re: Using XLOOKUP for 2 search criteria

Re: Using XLOOKUP for 2 search criteria

Re: Using XLOOKUP for 2 search criteria

Re: Using XLOOKUP for 2 search criteria

Re: Using XLOOKUP for 2 search criteria

Re: Using XLOOKUP for 2 search criteria

Re: Using XLOOKUP for 2 search criteria

Re: Using XLOOKUP for 2 search criteria

Re: Using XLOOKUP for 2 search criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] XLOOKUP with multiple search criteria and multiple return values

Problem with Xlookup's Wildcard search

[SOLVED] Xlookup for 2 criteria to search

[SOLVED] I want to understand how the option search last to first in XLOOKUP works

[SOLVED] Combing SUMIF SEARCH and XLookup

Xlookup search for value in multiple cols

[SOLVED] search data using xlookup function in excel?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions