Duplicate Vlookup

[whhaatt]

I am trying to do a Double nested vlookup where vlookup 1 looks for DOB in range and and vllokup 2 looks for surname in range, only when both vlookups match will it return the corresponding column value

=IF(VLOOKUP(Sheet1!A1,Sheet3!$A:$G,(VLOOKUP(Sheet1!F1,Sheet3!$F:$F,1,FALSE)),FALSE),"NULL")

I get #N/A

Sheet1
A B C D E F
D.O.B OTHER TITLE FORENAME MIDDLE SURNAME
01-01-10 0 MR JOE N BLOGGS

Sheet3
A B C D E F
D.O.B OTHER TITLE FORENAME MIDDLE SURNAME
03-04-11 1 MISS DORIS N BRIGGS
01-01-10 0 MR JOE N BLOGGS
01-01-10 0 MR DEREK N SMITH


I want the output to show record 2 details in Sheet 3, in this case the output should be

vlookup1 - 01-01-10
vlookup2 - BLOGGS

In other words only return results from sheet3 in row when 01-01-10 and BLOGGS appears in range row

Please help

 

[Brianwarnock]

Not clear what you are trying to return here but to do a 2 column lookup use Index and Match

=Index(resultarray,match(firstcriteria&secondcriteria,firstarray&secondarray,0))

You would need to do this twice to get both values.

Brian

 

[NBVC]

I too can't see what you are trying to return...

the following:

=ISNUMBER(MATCH(1,INDEX((Sheet3!$A$1:$A$100=A1)*(Sheet3!$F$1:$F$100=F1),0),0))

will return TRUE if a match is found or FALSE if match is not found.

If there is another item, say from Sheet3, column G you want returned based on a match, then something like...

=INDEX(Sheet3!$G$1:$G$100,MATCH(1,INDEX((Sheet3!$A$1:$A$100=A1)*(Sheet3!$F$1:$F$100=F1),0),0))

 

[whhaatt]

Thanks for the reponse

i am trying to lookup to values in a row against a range to see if the same 2 values appear in the other spreadsheet in a the same row, and return a column value from that row.

eg: - search for MR(title)(A1) and Bloggs(surname)(B1) spreadsheet 3 - when both are found in the same row, return the d.o.b from the matched row

 

[NBVC]

The second of my formulas should do that for you....something like

=INDEX(Sheet3!$A$1:$A$100,MATCH(1,INDEX((Sheet3!$C$1:$C$100=C1)*(Sheet3!$F$1:$F$100=F1),0),0))

Just change the sheetname, and ranges to suit.

 

[Brianwarnock]

As will mine , something like

=INDEX(Sheet3!A1:A7,MATCH(A1&B1,(Sheet3!D1:D7)&(Sheet3!F1:F7),0))

perhaps NBVC can say which is the better and why?

BTW whhaatt your first example was not what you finally posted and did muddy the waters.

Brian

 

[NBVC]

Brian,

For yours to work you need to actually confirm the formula as an Array formula by holding the CTRL+SHIFT keys down and hitting ENTER key... which will make the formula be enclosed in { } brackets.... otherwise you get the #VALUE! error...

My formula does not need that and can be confirmed only with ENTER as all other regular formulas...

 

[Brianwarnock]

NBVC

Ah! thanks, I now realise that I hadn't told whhaatt that it was an array formula, and to be honest i had assumed that yours was also.

Brian

 

[whhaatt]

SOLVED

Thanks for the responses

I thought out of the box and used the following method

in Sheet 1 I concatanated the cells that i wanted to use in the search

EG:
A1 - MR
B1 - JOE
C2 - BLOGGS

IN G1 - =A1&" "&B1&" "&C2

SO G1 WILL contain the following output - Mr Joe Bloggs

I did the same concatanate in Sheet 3 for the same fields(INPUT CONCATNATE IN COLUMN A).

I then did a vlookup on the concatanate cells

=vlookup(G1,Sheet3!A:E,5,FALSE)

COLUMN 5 CONATINS D.O.B

All working now - This way i can do infinate search criterium (2,3,45... level vlookups using concatanate)

 

[NBVC]

Yes, usually that is the easiest way.

But I find we can never win the battle... when I suggest using helper columns, most OP's ask if we can do it without the helper columns.... and then the opposite, when I try to combine the result into one formula, the user finds it too complex and would rather work with the helper columns.... it's a toss up!.

 

[Brianwarnock]

Concatanation! isn't that what the Index and Match did, but on the fly.

Brian