V / XLOOKUP issue

[ollyhughes1982]

Hi,

I have a simple formula, which I cannot work why is not working. Please could someone let me know what I’m doing wrong. Seems like a simple v or xlookup solution to me.

I just want to search for “Chris” in B14:F15 and return the value from G14 if it is there. it works when “Chris” is in B14, but not when in B15. Strange!

Thanks in advance!

OneDrive link to file: Pool Team (Division 1, 2023-2024) WORKING.xlsx

Olly.

 

[Fluff]

It should be B14:B15 not B14:F15

 

[Candyman8019]

Also, your G14:G15 are merged. Unmerge those and it should work.

 

[ollyhughes1982]

It should be B14:B15 not B14:F15

Ah yes, I forgot to amend it from where I was using vlookup previously. Is there no way to get it to work without unmerging? Can I just use G14 as the reference? like this:

=XLOOKUP("Chris",'Match Card # 1'!$B$14:$B$15,'Match Card # 1'!$G$14)

Tried this, but just get a value error

 

[Candyman8019]

the problem is Chris is on line 15...so Excel should return the value from line 15...however, since it's merged, that's not possible.

 

[ollyhughes1982]

OK, I thought as they were merged it would pick whatever was in there

 

[Fluff]

Try it like

=IF(ISNUMBER(XMATCH("Chris",'Match Card # 1'!$B$14:$B$15)),'Match Card # 1'!$G$14,"")

 

[ollyhughes1982]

Try it like

=IF(ISNUMBER(XMATCH("Chris",'Match Card # 1'!$B$14:$B$15)),'Match Card # 1'!$G$14,"")

Yes, that works. Superb, thanks. Caught you off-guard with a non-parkrun-related question for once!

 

[Fluff]

Glad we could help & thanks for the feedback.