Hi All,
I have a vlookup table that contains data below, I may have more than 1 value, if I run a vlookup it will find the first one and display....how can/should I go about determining either the higher price or if this match has multiple values?
Code:=vlookup(mydata, mylookup,3,false) A - B - C eq# - storloc# - price 13 - 1111 - $20 13 - 1323 - $22 13 - 1200 - $10 14 - 1111 - $15 14 - 1200 - $25 15 - 1323 - $10 16 - 1200 - $12
Well with a vlookup, you are really comparing one list of data to another. However if you wanted to determine which prices are higher than a certain amount, an IF statement might be best. You could even use it in combination with a vlookup. Since a non-match always returns #N/A! which is an error, you can check for this for example:
This returns a blank instead of N/A, but you could make that value whatever you want. Of course I don't entirely understand what you need, so I am guessingCode:=IF(ISERROR(VLOOKUP(A1,B:B,1,0)),"",VLOOKUP(A1,B:B,1,0))But if you can elaborate a little, I am sure that I can help.
"The Enrichment Center is required to remind you that first you will be baked, then there will be cake." - GLaDOS
on tab 1 i will have the following:
On tab 1 column B, I want to pull in from the vlookup table the match where the cost (column C - tab 2). In post#1 is how the vlookup appears on tab 2.Code:A EQ# 13 14 15 16
and on tab 1 column C, I want to pull in how many matches found....so result would be as follows:
Code:A - B - C EQ# 13 - $22 - 3 14 - $25 - 2 15 - $10 - 1 16 - $12 - 1
Oh I see. It looks like you need a pivot table then. That would summarize for you the number of rows with a certain cost. A vlookup in this way (comparing multiple costs) would be cumbersome, because to get an accurate count you'd have to run a separate vlookup for each cost. You'd be better off with a pivot table that would give you a summary by EQ# of each cost. I can walk you through that if needed. The only other option is a separate COUNTIF for each cost, and that would not be very efficient either.
"The Enrichment Center is required to remind you that first you will be baked, then there will be cake." - GLaDOS
Posting Permissions 
LinkBack URL
About LinkBacks
Reply With Quote
Bookmarks