Vlookup multiple cell help

[ryan4646]

Dear all,

i have one two sheet. in one sheet i have blank columns only without column A. i want to input rest of the other columns exactly matching with COLUMN A from sheet 2 with one vlookup formula. i can do it by seperate v lookup by everytime but i am sure there is another way to input B,C,D,E,F by matching with A from sheet 2 using multiple criteria formula. can anyone help me to teach or insert there please. attaching file.

[Fotis1991]

Hi

The numbers in MAT column in Sheet1(except numbers 1 & 2) does not match with the numbers in MAT column in Sheet2.

Do you want the results only for numbers 1 & 2?

[kvsrinivasamurthy]

Use this in B2 of sheet1 and drag across and down.

Please Login or Register  to view this content.

[ryan4646]

Hi fotis,

just 1,2 and 7 is matching. I just want those match. Nothing else

[ryan4646]

Hi KVSRINIVASAMURTHY i use, excel 2003, could you please give me correct one, becuase as i know iferror is not compatible in 2003 version.

[Fotis1991]

So ryan, in B2 and copy down use this.

=IFERROR(INDEX(Sheet2!B$2:B$10,MATCH($A2,Sheet2!$A$2:$A$10,0)),"")

[Fotis1991]

For Excel 2003

=IF(ISERROR(INDEX(Sheet2!B$2:B$10,MATCH($A2,Sheet2!$A$2:$A$10,0))),"",INDEX(Sheet2!B$2:B$10,MATCH($A2,Sheet2!$A$2:$A$10,0)))

[ryan4646]

Dear fotis,

thanks. It is solved now. Can you please teach me step by step how to do that. Actually it is working but i dont know how to do that

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

[Fotis1991]

You are welcome and thanks for the rep!

Actually this is a simple INDEX & MATCH function.

=INDEX(Sheet2!B$2:B$10,MATCH($A2,Sheet2!$A$2:$A$10,0))

INDEX(Sheet2!B$2:B$10 ,is the part of the formula that we say to the formula the range that the expected results are beeing....

MATCH($A2,Sheet2!$A$2:$A$10,0))

The MATCH part of the formula is the one that we say to the formula in which range has to look for matching the value that exists in A2.

It's important also to understand what dollar($) sign does.

If you see in INDEX part INDEX(Sheet2!B$2:B$10 there is only 1 $ AFTER the column letter. This as you drag to the right becomes INDEX(Sheet2!C$2:C$10...AND SO ON..

The second MATCH part of the formula is locked. Sheet2!$A$2:$A$10....Dollar sign exists in front and behind of column letter. This means that this renge never change when you drag dawn or across the formula.

The first MATCH part of the formula is locked only for dragging across. The $ sign is in front of the column letter.This means that as you drag down the formula this changes to $A3..$A4 AND SO ON...BUT wwhen you drag acroos does not change..

The IFERROR function for Excel >2003 and IF(ISERROR, for Excel <2007 have a different syntax but both offer the same result. Don't let the formula to give #N/A values when there is nothing to match..

Also see these links.

http://exceluser.com/blog/1107/why-i...-in-excel.html

http://office.microsoft.com/en-us/ex...001231765.aspx

http://www.excel-examples.com/iserror.htm

http://www.cpearson.com/excel/relative.aspx

[ryan4646]

Try this

Please Login or Register  to view this content.

hi kvs,

the issue is already solved, but i as there was two soulution, one is via vLOOKUP and other is via index match function, i would really like to learn both of the procedure. Fotis already gave his explanation of index match perfectly. can i expect it from you too about the vlookup that you done which is also correct. thanks

[Fotis1991]

Ryan as you wait for KVS to reply to your question i have to say that the first link that i provided has an excellent article explaining why is better to use INDEX & MATCH rather LOOKUP.

[ryan4646]

Ryan as you wait for KVS to reply to your question i have to say that the first link that i provided has an excellent article explaining why is better to use INDEX & MATCH rather LOOKUP.

the link was really helpful. thanks.

[Fotis1991]

........................