vlookup 2 cells at the same time

[hsandeep]

I am using vlookup to get the values from another workbook.
Is it possible:
i use 2 Lookup_value & fetch the matching values.
In workbook1: I want to Lookup_value A1 with another workbook2 AND ALSO Lookup_value B! with workbook2 & then fetch a vaue lying in the 20 column (Colm_index_num).

How to accomplish it?

 

[hsandeep]

How to do: a null "" should appear if not matched?

 

[Robert Mika]

Try like this:
=IFERROR(VLOOKUP(A1&B1,Workbook2!A1:F999,6,0),"")

 

[soccerkingpilot]

Workbook1:

A1=PA1000 & C1=TASK1

Workbook2:
A1=PA1000 & C1=TASK1 F1=999

I am able to vlookup 999. ok

Workbook1:

A1=PA1000 & C1=TASK1

Workbook2:
A1=PA1000 & C1=TASK2 F1=999

I should not get 999 instead a null "" should appear.

Am I clear this time Or I can still give you more example.
Pls ask yr queries?

You can create layers with VLOOKUPS and IF statements. Check my syntax, but you could do IF(AND(VLOOKUP(A1,Book1,1,false)=A1,VLOOKUP(A1,Book1,3,false)=C1),F1=VLOOKUP(A1,Book1,6,false),"")

What I'm doing is using an If/else statement for the F1 value or null. Inside, I'm using vlookup to make sure A1 matches in both books and another vlookup to make sure C1 matches in both books. If both are true, then F1 will be printed in the cell that you choose (I labeled if F1 in this case).

 

[taurean]

TAUREAN,

Please give formula for point#2.

As I said before there are many approaches, see this thread.

And for your case the Array formula will be [CTRL + SHIFT + ENTER]:
=INDEX([Workbook2]Sheet1!$A$1:$F$999,MATCH(A1&B1,[Workbook2]Sheet1!$A2:$A999&[Workbook2]Sheet1!$B$1:$B$999,0),6)

You will have to adjust the range references!

 

[hsandeep]

IF(AND(VLOOKUP(A1,Book1,1,false)=A1,VLOOKUP(A1,Book1,3,false)=C1),F1=VLOOKUP(A1,Book1,6,false),"")

In above formula, should the 3rd A1 be C1 i.e. in VLOOKUP(A1,Book1,3,false)=C1?
Please reply. Thanks for this way of multi vlookup.

 

[hsandeep]

And for your case the Array formula will be [CTRL + SHIFT + ENTER]:
=INDEX([Workbook2]Sheet1!$A$1:$F$999,MATCH(A1&B1,[Workbook2]Sheet1!$A2:$A999&[Workbook2]Sheet1!$B$1:$B$999,0),6)

You will have to adjust the range references![/QUOTE]
taurean,
Where is Workbook1's data? Or it can also be asked as where is [Workbook2]Sheet1 data? You are very near to goal. Please explain more to make me incorporate correct formula. Thanks for your extra help taurean, but please do help. I even intend to use 4 vlookup.

 

[soccerkingpilot]

IF(AND(VLOOKUP(A1,Book1,1,false)=A1,VLOOKUP(A1,Book1,3,false)=C1),F1=VLOOKUP(A1,Book1,6,false),"")

In above formula, should the 3rd A1 be C1 i.e. in VLOOKUP(A1,Book1,3,false)=C1?
Please reply. Thanks for this way of multi vlookup.

What that is doing is checking to make sure the value of the third column in book1 is the same as the third column of whatever sheet you're in. It all depends on where you want the data to go...post an example worksheet if you want a more specific answer.

-Austin

 

[taurean]

And for your case the Array formula will be [CTRL + SHIFT + ENTER]:
=INDEX([Workbook2]Sheet1!$A$1:$F$999,MATCH(A1&B1,[Workbook2]Sheet1!$A2:$A999&[Workbook2]Sheet1!$B$1:$B$999,0),6)

You will have to adjust the range references!

taurean,
Where is Workbook1's data? Or it can also be asked as where is [Workbook2]Sheet1 data? You are very near to goal. Please explain more to make me incorporate correct formula. Thanks for your extra help taurean, but please do help. I even intend to use 4 vlookup.[/QUOTE]
You have put this data in Workbook1's sheet where you want to. Blue part refers to workbook1 data. Did you try it or just asking?

 

[taurean]

And for your case the Array formula will be [CTRL + SHIFT + ENTER]:
=INDEX([Workbook2]Sheet1!$A$1:$F$999,MATCH(A1&B1,[Workbook2]Sheet1!$A2:$A999&[Workbook2]Sheet1!$B$1:$B$999,0),6)

You will have to adjust the range references!
taurean,
Where is Workbook1's data? Or it can also be asked as where is [Workbook2]Sheet1 data? You are very near to goal. Please explain more to make me incorporate correct formula. Thanks for your extra help taurean, but please do help. I even intend to use 4 vlookup.

My last post seems bit odd.
Red part ----> 2nd workbook. [You will have to adjust these references to suit your requirement]
Blue part ----> 1st workbook. [Simply put this formula in 1st workbook]
If you find it difficult then you will have post exact names and layout!

Edit: The previous post has a QUOTE more which came in when I tried to post with the OP's quotes. Request to MOD's: please delete previous post [#18]!