Solved: Help with Lookup Function

[nick99]

I am trying to use a simple excel function here ?Lookup?.
I have used this function several times; however in this case I am having a lot of problems.

I have two sheets. Sheet 1 has ?T-Codes? and Sheet 2 has ?T-codes? and ?VIN#?s?.
I have to basically pull the respective VIN #?s from sheet 2 for the T-Codes on sheet 1.

Formula on sheet 1. Cell A1
LOOKUP(E2,Sheet2!B:B,Sheet2!A:A)

However the problem I am running into is; if Lookup cannot find the T-Code on sheet 2, it gives me some arbitrary VIN# value.
Also sometimes there will be a T-Code on Sheet 2, but lookup will be confused and give me a zero value on sheet 1.

I have arranged columns on both the sheets in an ascending manner and the category defined for all the columns is ?General?

The advanced problem I have is I am trying to create a template, so these numbers will change on an everyday basis and lookup has to work efficiently each time.
The data is going to be extensive and I cannot afford to give a wrong VIN# to a T-code.

Please refer to the attachment

Is there some other way I can handle this situation?

Thank you all

[rory]

Try this formula in A2 and copy down:

=IF(COUNTIF(Sheet2!B:B,E2)=0,"",INDEX(Sheet2!A:A,MATCH(Sheet1!E2,Sheet2!B:B ,0)))

HTH
Rory

[nick99]

Awesome, it works. I hope its robust.
Can you please explain me what you exactly did?
How does the Index and match function work?

[rory]

Sure. The COUNTIF part checks to see if there is a match (if not, the MATCH function would return a #N/A error). If there isn't it returns "", otherwise we get to the INDEX/MATCH part.
The MATCH function finds the position within column B on sheet2 where the value in E2 on Sheet1 can be found. The 0 as the third argument says that we only want an exact match, which avoids the problem you had with LOOKUP. The INDEX function then retrieves the value from that row in column A on Sheet2.
You can't use VLOOKUP which would have been the alternative solution because VLOOKUP will only look values up in the first column of the lookup table and your lookup values are in the second column.

HTH
Rory

[nick99]

Thanks a Lot!!

[nick99]

Another question!!
What if i want the formula to generate only if there are values in the other cell.
Eg:

Column A Column B
1 Formula Value
2 Formula Value
3 Formula Value
4 No Formula No Value

Since the values are dynamic, the number of rows will change on a regular basis.

[Bob Phillips]

=IF(OR(B2="",NOT(COUNTIF(Sheet2!B:B,E2))),"",INDEX(Sheet2!A:A,MATCH(Sheet1! E2,Sheet2!B:B,0)))