vlookup- approximate match

[drewbny]

I tried using vlookup with approximate match and my vlookups werent working. I have to lookup a column which is a concatenate of two other columns. does vlookups on approximate match not work with concatenates?

 

[drewbny]

i followed your directions and im still encountering trouble. For example my formula is listed below.

,IF(ISNA(INDEX(ladder21,(MATCH($M37,Ladders,1)))),0,(INDEX(ladder21,(MATCH($M37,Ladders,1))))))


Ladder 21 is the range =Ladders!$U$6:$U$27027. this range holds february wk 2 sales that i want from the Ladders tab of my workbook
Ladders is the range =Ladders!$A$6:$A$27027 . this range is the concatenate of product group 1506 class 5403 and metric Plan sales $. this is my lookup column of all of my different product group and class combinations


On my main data tab here are the cells listed below in the index-match formula I am using to pull information from the Ladders tab

cell M37 is the following formula =CONCATENATE($B37,F37).
result of formula says 15065403Plan Sales $

Cell b37 is the following formula =CONCATENATE(RIGHT(A37,8)) the result is 15065403
Cell A 37 has the following text CL15065403
Cell F37 says Plan Sales $

for some reason this product group 1506 class 5403 pulls 0 as the answer. when i type in product group 1506 class 5401. i get the correct answer. when i put product group 1506, class 5403 under exact match i get the correct answer. Please advise? i sorted the data in the Ladders tab by column B, then column D, then column F in ascending order.

column A in my ladders tab which is my name range called Ladders has the following formula =CONCATENATE(B513,D513,F513)
column B is product group # which is 1506, column D is class # which is 5403, and column F is the sales metric which reads Plan Sales $. Could the problem be that my source data in column D has blank data for the product group totals?

 

[Aladin Akyurek]

i followed your directions and im still encountering trouble. For example my formula is listed below.

,IF(ISNA(INDEX(ladder21,(MATCH($M37,Ladders,1)))),0,(INDEX(ladder21,(MATCH($M37,Ladders,1))))))


Ladder 21 is the range =Ladders!$U$6:$U$27027. this range holds february wk 2 sales that i want from the Ladders tab of my workbook
Ladders is the range =Ladders!$A$6:$A$27027 . this range is the concatenate of product group 1506 class 5403 and metric Plan sales $. this is my lookup column of all of my different product group and class combinations


On my main data tab here are the cells listed below in the index-match formula I am using to pull information from the Ladders tab

cell M37 is the following formula =CONCATENATE($B37,F37).
result of formula says 15065403Plan Sales $

Cell b37 is the following formula =CONCATENATE(RIGHT(A37,8)) the result is 15065403
Cell A 37 has the following text CL15065403
Cell F37 says Plan Sales $

for some reason this product group 1506 class 5403 pulls 0 as the answer. when i type in product group 1506 class 5401. i get the correct answer. when i put product group 1506, class 5403 under exact match i get the correct answer. Please advise? i sorted the data in the Ladders tab by column B, then column D, then column F in ascending order.

column A in my ladders tab which is my name range called Ladders has the following formula =CONCATENATE(B513,D513,F513)
column B is product group # which is 1506, column D is class # which is 5403, and column F is the sales metric which reads Plan Sales $. Could the problem be that my source data in column D has blank data for the product group totals?

You are not going to re-do post #3, are you? That post does not use Index/Match, neither Concatenate.

 

[drewbny]

IN post 3 you wrote the following code

=IF(LOOKUP(G2&"|"&H2,$D$2:$D$6)=G2&"|"&H2,LOOKUP(G2&"|"&H2,$D$2:$D$6,$C$2:$C$6),"Not Found")

do i have to write G2&"|"&H2 exactly? why doesn't concatenate work?

 

[Aladin Akyurek]

IN post 3 you wrote the following code

=IF(LOOKUP(G2&"|"&H2,$D$2:$D$6)=G2&"|"&H2,LOOKUP(G2&"|"&H2,$D$2:$D$6,$C$2:$C$6),"Not Found")

It's not "code", rather a darn fast formula for exact match...

do i have to write G2&"|"&H2 exactly?

Yes.

why doesn't concatenate work?

Calling CONCATENATE is more expensive than using &...

 

[drewbny]

what do you mean by more expensive? does it take longer to calculate by using the concatenate function?

 

[Aladin Akyurek]

what do you mean by more expensive? does it take longer to calculate by using the concatenate function?

Yes...

 

[drewbny]

thanks alot Aladin. i understood your example much better now

 

[drewbny]

HI, i spoke with my coworkers and they dont want to sort the data each time we download it. What is the best way to get rid of a double lookup without using the ISNA function? Currently i was using isna with index match

 

[Aladin Akyurek]

HI, i spoke with my coworkers and they dont want to sort the data each time we download it. What is the best way to get rid of a double lookup without using the ISNA function? Currently i was using isna with index match

That's a pity...

What is the formula with ISNA that you are using?

 

[drewbny]

The IF statement that I have which has the ISNA is the following

=IF($AT$4>0,IF(ISNA(INDEX(projsls,(MATCH($B18,Prjsheet,0)))),0,(INDEX(projsls,(MATCH($B18,Prjsheet,0))))/1000),IF(ISNA(INDEX(ladder20,(MATCH($L18,Ladders,0)))),0,(INDEX(ladder20,(MATCH($L18,Ladders,0))))))

projsls is a named range
Prjsheet is a named range
Ladders is a named range
Ladder 20 is a named range

Originally I wrote a version of this formula as a vlookup but i switched my formulas to index match since i could set up a named range that only looked up one column as opposed to my vlookup formula that looked up an entire worksheet in my named range. By using smaller named ranges, will this make my workbook calculate faster?