Hi



I am trying to get this formula to work, but its giving me an incorrect value-

=IF(XMATCH($E2,All!E:E,0)=XMATCH($B2,All!B:B,0),"All!E"&XMATCH($E2,All!E:E,0),FALSE)

The expected answer should be the value stored in All!E210

Actual answer is the text value of "All!E210"

How can I fix this. This has been driving me crazy for the last 2 days!! Please help!!

Hi
please post a sample sheet ( no pics please) with some data and some manually calculated results.Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Please find attached. I have a master spreadsheet called "All" and a "Data" sheet. I need to bring the right data from the "All" spreadsheet to the "Data" spreadsheet based on a criteria that column B and E should match in both the sheets.The results need to go into G-K

Attached Files

Test.xlsx (191.0 KB, 9 views)

It depends what you're trying to do.
It looks as though you're comparing 2 columns in each table to find matches:
A formula at cell G2 (copied down) does this and that may be all you need.
However, you may have a problem; there may be more than one row in the All sheet that matches the two columns in the Data sheet. I've highlighted which rows this applies to in column N where if there's a value >1 there are multiple matches. In a few instance I've shown those matches in column O at rows 28, 34 and 512 (where there are 3 matches).
So are you looking to return only complete matches, or maybe on more than 2 columns?


Edit: I've just noticed you're using Office 2016 so some of the functions I've used may not work, although the results of those formulae should still show. Tell me if the G2 formula fails and I'll re-write.

Attached Files

msofficeforums49833Test.xlsx (197.3 KB, 3 views)

Maybe
But something feels wrong there! You want to return from sheet All the value from column E (All!E210="Applications"), which must be same same as value in cell Data!E2 ("Applications"), plus some additional conditions! So why all this calculation? Why not simply