Lookup One Value But in Multiple Columns

I need to lookup a single value but search in multiple columns. Then I want to return values from different columns. It's like doing an XLOOKUP or VLOOKUP except the value I'm looking for is in separate columns and so is the value I want to return. I have attached an example. Each row of data contains patient information and the different tests they've. In another file, I have just the test serial number. So I need to match up the data from the 2 lists using the serial number and determine what type of test it was. There's only 1 row per patient so if they have taken 2 or 3 tests (max is 3), the first test will be in the first column with the type if text int he next and so on. I've tried nesting V and XLOOKUPs but it doesn't work. Any suggestions?

Originally Posted by hrlngrv

B3:

Formula:

Please Login or Register  to view this content.

filled down as far as needed. If the ID in A3 doesn't exist in any of the 3 tables, this returns #N/A. If you want something else, wrap the 3rd VLOOKUP call in a 3rd IFERROR call.

Tangent: FWIW, perhaps MSFT could steal a feature from Google Sheets, in which this could be accomplished using

B3:

Formula:

Please Login or Register  to view this content.

Thanks. That works nicely! It doesn't seem to make a difference that the lookup columns aren't sorted in ascending order. I always thought hat was required for VLOOKUP.

Originally Posted by SweetBaboo

. . . It doesn't seem to make a difference that the lookup columns aren't sorted in ascending order. . . .

That's what VLOOKUP's 4th argument determines. When it's 0 or FALSE, VLOOKUP performs exact matching on 1st columns which don't need to be sorted; exact in italics because the 1st argument could include ? and * wildcards, so not exactly exact.