LookUp

**ptripathi** · 11-27-2011, 11:27 PM

Hi,
i have a table in two sheets in same workbook. I want to check whether the Column A items in sheet 3 are present in column A of Sheet 2, if yes, i want them to get shorted with either true or false or in same order with ref (Column A of Sheet3). Since, i have 2370 items in both column As' of sheet 2and 3 so , i am not sure how it will work.

Thanks

**darkyam** · 11-28-2011, 01:34 AM

I'm not sure I follow. Could you please upload an example workbook with dummy data (but with the exact same format), along with a few desired results? Thanks.

**ptripathi** · 11-28-2011, 04:24 PM

Sorry for the delay and thanks for your concern.
I am attaching the dummy sheet, hope that makes some sense to you.

**darkyam** · 11-28-2011, 07:46 PM

I understand the first part, but don't get the second. In sheet1, B2 and down, put =IF(ISERROR(MATCH(A2,Sheet2!A:A,0)),MAX(Sheet1!$B$1:B1)+1,""). Then in G3 and down, put =INDEX(A:A,MATCH(ROWS($G$3:$G3),B:B,0)). This will return the numbers that are not in Sheet2!A.

I didn't understand this line, though:

Also I want to put the vlaues of C1 of sheet 2 in front of the match in sheet1, e.g row20.

Can you give an example of the precise result you want and where you want it?

**ptripathi** · 11-29-2011, 01:01 AM

That means that if you se the C1 in sheet 2 has corresponding values for the numbers in B1. So if i find the math/same number in A1 than i want to put the same alphabetical value in front of number present in A1.

Does it makes sense?

Thanks

**darkyam** · 11-29-2011, 01:06 AM

Try =IFERROR(VLOOKUP(A2,Sheet2!A:C,3,0),"")

**ptripathi** · 11-29-2011, 01:18 AM

it is giving me some short of numerical value.
What does that 3 stands for?
Would you mind to read that formula for me,meaning i want to know what that formula says.
Sorry, i m new to such world of excel.
Thanks

**darkyam** · 11-29-2011, 01:33 AM

The first value is what it is looking up in the table. The second is the range in which it is looking (or, more specifically, it looks in the first column of that range). The 3 tells the formula which column to return the value from. In other words, with a range of A:C, a value of three tells the formula to match the value to column A and return the value on that row from column C. The 0 means that the value it's looking up has to match a value in A exactly. In other words, 1 does not equal 1.0000001. You could use 1 as the last argument to have it look up the closest value that is not greater than the lookup value. (TRUE and FALSE can replace 1 and 0, respectively.)