Vlookup

[oddworld]

hi all what i am trying to achieve is a summary page populated by t2 tables. ie i have a unique field in column a, i then need vlookup or macro to look at the uniquie value in column a and search in table "cfts" or table"ue" and populate data from the applicable table. The unique value will only exist in 1 of the tables or not at all.
I can't seen to get vlookup to lookup more than 1 table? do i need a macro for this type of search

 

[Aladin Akyurek]

hi all what i am trying to achieve is a summary page populated by t2 tables. ie i have a unique field in column a, i then need vlookup or macro to look at the uniquie value in column a and search in table "cfts" or table"ue" and populate data from the applicable table. The unique value will only exist in 1 of the tables or not at all.
I can't seen to get vlookup to lookup more than 1 table? do i need a macro for this type of search

What kind of data, numeric or text, are you fetching?

 

[DonkeyOte]

I'll leave to the master that is Aladin, however, all I would say is you could think about using a MATCH with ERROR to utilise a variable range name in your VLOOKUP or whatever...

=VLOOKUP(criteria,IF(ISERROR(MATCH(criteria,firstrangename,0)),secondrangename,firstrangename),column,FALSE)

assumes ranges are identical in terms of column positions for return values etc...

 

[oddworld]

the unique id is a number, and i am using a "=VLOOKUP(A1,ue,2,0)" to extract name and a few other details. The problem is how do i make the statement once it has checked the range "ue" and not found a match, i would like it to search the range "cfts". Second prob would be i would need to know which range was the data found in.

 

[Aladin Akyurek]

the unique id is a number, and i am using a "=VLOOKUP(A1,ue,2,0)" to extract name and a few other details. The problem is how do i make the statement once it has checked the range "ue" and not found a match, i would like it to search the range "cfts". Second prob would be i would need to know which range was the data found in.

If a numeric outcome is expected...

=LOOKUP(9.99999999999999E+307,
    CHOOSE({1,2,3},
      0,
      VLOOKUP(A1,cfts,2,0),
      VLOOKUP(A1,ue,2,0)))

If a text outcome is expected...

=LOOKUP(REPT("z",255),
    CHOOSE({1,2,3},
      "",
      VLOOKUP(A1,cfts,2,0),
      VLOOKUP(A1,ue,2,0)))

What are the ranges ue and cfts currently refer to?

 

[DonkeyOte]

Aladin, could you explain or point me in right direction as to how your formulae work ?

I've read through the Help etc but can't quite fathom the mechanics of what it does...

I don't often see things here I don't understand but the above has me flummoxed!

I can see that it's trying to find the largest answer but is this in array formula ? ie how is it generating the 3 answers - 0, range1, range2 etc ?

Not to worry - worked it out... NICE!