Nested vlook and match functions

[jbop]

Fantastic forum! I registered to share my problem and seek help from everyone.

In Tab 1, two columns:
If column one contains data A then the second is empty.
If column one is empty then the second contains data B.

In tab 2, two columns: I have a list of data A and a corresponding name for each. In tab 3, ditto.


In tab 1, I am creating a third column. I need Excel to look in in each row column 1 and 2 and to go for look the data it finds, in tab 2 and 3. If it finds a match, bring back the corresponding name and place it on the same row in column 3 in tab 1.

I hope this is clear. Let me know if it isn't please.

[DonkeyOte]

So what is the order of preference... ie if A/B value from Tab 1 is found in A on Tab 2 do you disregard whatever may have been found on Tab 3 ... ie what is the search order ?

eg

Tab1!C1: =LOOKUP(REPT("Z",255),CHOOSE({1,2,3},"",VLOOKUP(A1&B1,Tab3!A:B,2,0),VLOOKUP(A1&B1,Tab2!A:B,2,0)))

[jbop]

Holy smokes, you are good!

To answer your question, in this case, the data found in Column 1 can only be found in tab 2 and the data from Column 2 only in tab 3.
So in tab 1, the formula needs to check for each row which column has content (it's either, or: none have content in both column 1 and 2) and then based on the type (numbers in column 1, acronym in column 2), match it and fetch the associated name from either tab 2 or 3.

[DonkeyOte]

The formula provided should work but is perhaps then over engineered for your requirements if you're not fussed about error handling, eg:

Please Login or Register  to view this content.

if you want to handle errors then you may as well keep with what you have from the prior post IMO (you change the Null ("") to be whatever you want to appear - eg "No Match") ... this is all based on your assertion that B on Tab's 2 & 3 contain names (ie strings)

[jbop]

I get #N/A with

=VLOOKUP(A1&B1,IF(LEN(A1),Tab2!A:B,Tab3!A:B),2,0)

I am attaching the spreadsheet I am working on. That will probably be easier this way.

[DonkeyOte]

Given the nature of your data I would revert to the earlier example (albeit with modification)

Please Login or Register  to view this content.

where blank, no match is found (& to reiterate you can change the "" to be whatever string you would like returned when no match found)

[jbop]

Thank you so very much DonkeyOte!

This worked. You also taught me functions I didn't know in the process.

Why did you use REPT Z 255 times?

I don't understand the use of the choose function in this case either CHOOSE (1,2,3) .

[DonkeyOte]

Why did you use REPT Z 255 times?

REPT("Z",255) as you infer creates a string of Z repeated 255 times which is as you can imagine a pretty "big" string... given "ab" > "a" thus "zzzz..." > other strings...

Why use it ? Well LOOKUP assumes all values in the lookup_vector to be sorted in ascending order, so...

in the case of numbers:

1,2,3 etc

in the case of strings

a,ab,b etc...

the default function of LOOKUP is to find the greatest value in the lookup_vector which is <= criteria value.
Given the assumption of the values being in ascending order it will assume therefore that the last value in the lookup_vector is the greatest and thus if the criteria > last value it returns the last value... eg:

Please Login or Register  to view this content.

given the criteria ("d") is bigger than all values in the lookup_vector LOOKUP actually returns "a" given it assumes the list is to be sorted in alphabetical order and therefore deems the last value "a" to be the biggest value in the lookup_vector (in reality we know this is not the case) and given "a" < "d" returns "a" as this is seen as biggest value < d in the vector.
If we were to change the criteria from being bigger than all values to a value <= biggest vlaue we would get a different result (binary search algorithm employed)

Please Login or Register  to view this content.

So if we want to return the "last value" in the vector using LOOKUP it is essential that the criteria exceeds all known values... this is why we use REPT("Z",255) as the criteria when looking for last text string, ie

Please Login or Register  to view this content.

I don't understand the use of the choose function in this case either CHOOSE (1,2,3) .

We're using CHOOSE to create a 3 value lookup_vector ... where the first value is a Null (our default text string if both VLOOKUP fail to return a valid answer), the 2nd value is the 2nd VLOOKUP and the final value is the 1st VLOOKUP.

Note the ordering of the values in the lookup_vector, we're listing our possibel results in order of least preference.. ie the Null is the result we want as a last resort so to speak.

Given the use of CHOOSE to populate the lookup_vector we end up with

Please Login or Register  to view this content.

We know that from earlier section the above will return the last text string found... so if vlookup1 returns a valid name then that will be the result, if it doesn't and returns an error LOOKUP will ignore this and move to vlookup2.... and if that too fails to return a valid answer it will return the Null, to illustrate:

Please Login or Register  to view this content.

The "beauty" of LOOKUP is as you can see that it in fact ignores Error values when evaluating.

The same approach can be used to find "last number" in a range, replacing REPT("Z",255) with 9.99999999999999E+307 - see http://www.xldynamic.com/source/xld.LastValue.html

[jbop]

Interesting: I admire your intimate knowledge of Excel.

I am planning on reading up on these functions. I am afraid you opened the Pandora's box for me! :-)

Thank you again for your help.