Values Lookup in excel

Solved/Closed
seekermay Posts 28 Registration date Wednesday January 27, 2010 Status Member Last seen May 27, 2013 - Feb 2, 2010 at 11:28 AM
seekermay Posts 28 Registration date Wednesday January 27, 2010 Status Member Last seen May 27, 2013 - Feb 2, 2010 at 12:19 PM
Hello,

i want to match sheet2 with sheet1 WHERE
sheet1 is the master sheet and has all columns. sheet2 has only two columns as in below demonstration and other are to be looked up in Sheet1(master sheet). values in "date" and "ID" may repeat but both together "date" and "ID" makes a unique value. so "Date" and "ID" are to be matched simultaneously(together). it should turn an null value (N/A) if find no match in Sheet1.

Sheet1:

Date ID value1 value2 value3 value4 value5
01-jan-10 123 012 1234 fhgyr yhgte45 hjyge2
02-jan-10 456 786 5785 ksjsi djdkej4 jddk
03-jan-10 789 467 3748 kljslk kljkjklj5 jksdk
04-jan-10 101 849 3839 lkjkjlk kljkjklj2 lkljkj56
05-jan-10 102 674 3748 kjkljkl dskkjlj7 kjd46
02-jan-10 101 648 6373 kjhkj kjhkjk4 kkj567
03-jan-10 456 748 7484 lkkljk kjhkljk7 kjl564
and more.......

Sheet2:

Date ID value1 value2 value3 value4 value5
01-jan-10 123 ?(012) ?(1234) ?(fhgyr) ?( yhgte45) ?(hjyge2)
02-jan-10 456 ?(786) ?(5785) ?(ksjsi) ?(djdkej4) ?(jddk)
04-jan-10 101 ?(849) ?(3839) ?(lkjkjlk) ?(kljkjklj2) ?(lkljkj56)
02-jan-10 101 ?(648) ?(6373) ?(kjhkj) ?(kjhkjk4) ?(kkj567)
03-jan-10 456 ?(748) ?(7484) ?(lkkljk) ?(kjhkljk7) ?(kjl564)

Pls help me out on this....Thanks
......seekermay
Related:

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 2, 2010 at 12:02 PM
Most easy route would be if you can add one column on Master sheet

If you can insert a new column at C and put the formula at C1 as =A1 & "|" & B1
drag this formula all the way down


Then in the sheet two you can use vlookup as
for your C2 column the formula would be
=VLOOKUP($A2 & "|" & $B2, Master!$C$1:$H$8, 2, FALSE)

The number "2" before "false" refers to relative position from the newly inserted column to your column titled "value1"
0
seekermay Posts 28 Registration date Wednesday January 27, 2010 Status Member Last seen May 27, 2013 5
Feb 2, 2010 at 12:19 PM
Thanks!!!! Its simple and worked perfect.

Thanks again.
0