Previous Topic Next Topic

Values Lookup in excel

[Solved/Closed]
Report
Posts
28
Registration date
Wednesday January 27, 2010
Status
Member
Last seen
May 27, 2013
 -
Posts
28
Registration date
Wednesday January 27, 2010
Status
Member
Last seen
May 27, 2013
 -
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

2 replies

Answer 1 / 2
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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"
Answer 2 / 2
Posts
28
Registration date
Wednesday January 27, 2010
Status
Member
Last seen
May 27, 2013
 5
Thanks!!!! Its simple and worked perfect.

Thanks again.
Ask a question