lookup formula

[Shlby6]

hello, i am having a problem with a lookup formula. here is what i have so far.

on sheet 1 i have the following columns

unit # description qty

and on sheet 2 i have the following columns

unit # pd fo cost

i want to get the pd, fo, and cost info on to sheet 1 by using the unit # as reference.

i have the following formula =LOOKUP(A30,sheet2!A$2:$A$672,NBV!$B$2:$B$672) in the next blank column on sheet 1.

this works except, when i have a unit # on sheet 1 (for example 5), but no unit # 5 on sheet 2 it goes to the nearest unit # to 5. I don't know how to make it be an exact match so it matches the unit #'s exactly on both sheets.

any help would be greatly appreciated.

thanks!

shelby

 

[Brian from Maui]

Have a look at VLOOKUP setting the 4th argument to 0 (False) for an exact match.

 

[Andrew Poulsom]

You need VLOOKUP for that. It has a fourth argument for an exact match:

=VLOOKUP(A30,sheet2!A$2:$B$672,COLUMNS(sheet2!$B$2:$B$672),FALSE)

 

[Shlby6]

now it is returning the wrong thing, i can't figure out where it is leading to
thanks!

shelby

 

[Aladin Akyurek]

hello, i am having a problem with a lookup formula. here is what i have so far.

on sheet 1 i have the following columns

unit # description qty

and on sheet 2 i have the following columns

unit # pd fo cost

i want to get the pd, fo, and cost info on to sheet 1 by using the unit # as reference.

i have the following formula =LOOKUP(A30,sheet2!A$2:$A$672,NBV!$B$2:$B$672) in the next blank column on sheet 1.

this works except, when i have a unit # on sheet 1 (for example 5), but no unit # 5 on sheet 2 it goes to the nearest unit # to 5. I don't know how to make it be an exact match so it matches the unit #'s exactly on both sheets.

any help would be greatly appreciated.

thanks!

shelby

If NBV!A:B is sorted in ascending order on column A, invoke:

=IF(LOOKUP(A30,NBV!A$2:$A$672)=A30,LOOKUP(A30,NBV!A$2:$A$672,NBV!$B$2:$B$672),"Not Found")

If sort in ascending order does not hold, try:

=INDEX(NBV!$B$2:$B$672,MATCH(A30,NBV!A$2:$A$672,0))

 

[Shlby6]

great, that worked. thanks so much

shelby