Need to look up value in another Sheet based on criteria in first sheet.
Here is what I'm trying to do:
In sheet 1, I have a a column that lists values: Example: Dog Cat Cow Pig In Sheet 2, each month I paste a table that is computer generated. It also lists these same values as well as a number in the column next to the subject: Example: Dog 2 Unicorn 20 Cat 5 Cow 7 Horse 9 Pig 1 You'll notice that based on this example, the two lists don't always match (alphabetical orderwise), and the data in the second sheet will expand and contract from month to month (i.e. animal names can change from month to month in sheet 2, but stay stable in sheet 1). I need a function that I can put in Sheet 1 Column B, that will look up the numberical value in Sheet 2 that corresponds to the subject (animal) listed in Sheet 1. Example, I want sheet one to automatically look up the number and insert it in the column next to the animal name: Dog 2 Cat 5 Cow 7 Pig 1 Is there a function I can put in the second column in sheet 1 that will do this? THanks! --Stacey |
Need to look up value in another Sheet based on criteria in first sheet.
One way ..
Assuming the source table is pasted in Sheet2, cols A & B, from row1 down In Sheet1, With the lookup values in A1 down Put in B1: =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,0)) Copy down Alternatively, a slightly longer variation which could be placed in B1: =IF(A1="","", IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"", VLOOKUP(A1,Sheet2!A:B,2,0))) (Above will return blank for any unmatched case, instead of ugly #N/As) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Stacey" wrote in message ... Here is what I'm trying to do: In sheet 1, I have a a column that lists values: Example: Dog Cat Cow Pig In Sheet 2, each month I paste a table that is computer generated. It also lists these same values as well as a number in the column next to the subject: Example: Dog 2 Unicorn 20 Cat 5 Cow 7 Horse 9 Pig 1 You'll notice that based on this example, the two lists don't always match (alphabetical orderwise), and the data in the second sheet will expand and contract from month to month (i.e. animal names can change from month to month in sheet 2, but stay stable in sheet 1). I need a function that I can put in Sheet 1 Column B, that will look up the numberical value in Sheet 2 that corresponds to the subject (animal) listed in Sheet 1. Example, I want sheet one to automatically look up the number and insert it in the column next to the animal name: Dog 2 Cat 5 Cow 7 Pig 1 Is there a function I can put in the second column in sheet 1 that will do this? THanks! --Stacey |
All times are GMT +1. The time now is 01:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com