Xlookup several columns with multiple lookup values

Hello,

I was wondering if there is a formula that involves the Xlookup formula whereby, I can look up a value from several columns in one worksheet (e.g. the first tab) to see if it matches a value from several columns in another work sheet (e.g. the second tab), then return the corresponding value from the second worksheet (e.g. second tab)?

Please see attached an example. In column E of the 'Outputs' tab, I would like to return the value from Column A of the 'Rankings' tab. To do this, I would like to look up all values from Columns A, B, C & D from the 'Outputs' tab and see if it matches the value in Columns B or C of the 'Rankings' tab. Is it possible to do this with the Xlookup formula?

Thanks in advance!

Try in E2 and copy down:

Formula:

Please Login or Register  to view this content.

Good luck!

Well, there's probably a shorter way, but I think this works:

In E2 and copy down:

Please Login or Register  to view this content.

Originally Posted by Gregb11

Well, there's probably a shorter way, but I think this works:

In E2 and copy down:

Please Login or Register  to view this content.

Thanks for your reply, Gregb11! Whilst I do prefer shorter formulas, the one you provided also works, so I can definitely use this one if I am unable to get the same results with a shorter formula. Thanks for your help!

Originally Posted by DJunqueira

Another not small solution (could be smaller with LET() function, but I don't have it because of Win7).

Formula:

Please Login or Register  to view this content.

Thank you for this, DJunqueira! Yes, this formula also works! I will keep this one in mind too if I can't find a shorter solution.

The one that was first proposed by Estevaoba worked very well and is ideal (as the formula is a lot more straight-forward and shorter). However, for some reason, it did not find a match on row 19 on my example workbook. Does anyone know why?

Thanks in advance!

Originally Posted by Eades1412

Thank you for this, DJunqueira! Yes, this formula also works! I will keep this one in mind too if I can't find a shorter solution.
The one that was first proposed by Estevaoba worked very well and is ideal (as the formula is a lot more straight-forward and shorter). However, for some reason, it did not find a match on row 19 on my example workbook. Does anyone know why?
Thanks in advance!

I would say that you can't compare 4 columns with 2 columns that way. If cell A is in Ranking column C you won't find it that way, also an order problem.

Thank you for your reply, since you want the answer in one column consecutively I don't see other better way...

Originally Posted by DJunqueira

I would say that you can't compare 4 columns with 2 columns that way. If cell A is in Ranking column C you won't find it that way, also an order problem.

Thank you for your reply, since you want the answer in one column consecutively I don't see other better way...

Ah I see! That's such a shame. I guess the longer formulas will have to do.

Thanks everyone for your helpful responses!