vlookup with more than one reference with the same name

E

eddie d

I have a "template" set up in a worksheet so I can review data in the same
format daily. I import data to the sheet and the "template" finds the data
and puts it in the right order/format as the import comes in formated
slightly different daily. I use vlookup to find the refrence and subsequently
populate the data/template. I have a few lines that have the same reference
name. The first name works as it supposed to, The second name gets populated
with the first names' data as it finds it first. I need the second name to
to find the second reference and the third to find the third can anyone help.
Here is the vlookup formula I use and works great...cant even tell you how
I got it to work it as I am a beginer with this.

=IF(ISNA(VLOOKUP($B78,I$1:N$384,3,FALSE))=TRUE,"0.00",VLOOKUP($B78,I$1:N$384,3,FALSE))
 
B

Bernie Deitrick

Eddie,

Array enter (enter using Ctrl-Shift-Enter) the formula

=IF(COUNTIF($I$1:$I$384,$B$78)>=ROWS($A$1:A1),INDEX($K$1:$K$384,LARGE(($I$1:$I$384=$B$78)*ROW($I$1:$I$384),COUNTIF($I$1:$I$384,$B$78)-(ROWS($B$1:B1)-1))),"0.00")

And copy down for as many rows as you need.

HTH,
Bernie
MS Excel MVP
 
T

T. Valko

=IF(ISNA(VLOOKUP($B78,I$1:N$384,3,FALSE))=TRUE,"0.00",VLOOKUP($B78,I$1:N$384,3,FALSE))

What type of data is your formula returning? Text, numbers, both? Judging by
your error trap it looks like the returned value is supposed to numeric.

When you quote numbers like this: "0.00", Excel evaluates that as a TEXT
string and not a number. You probably don't want to do that. Also, you don't
really need to test ISNA=TRUE. ISNA will return either TRUE or FALSE so
testing for either TRUE or FALSE is redundant. You can also replace the
range_lookup argument with 0. It means the same thing as FALSE.

=IF(ISNA(VLOOKUP($B78,I$1:N$384,3,0)),0,VLOOKUP($B78,I$1:N$384,3,0))

That won't solve your problem but that'll show you how to save a few
keystrokes in your formula. To solve your problem and make the best
suggestion I need to know what type of data the formula returns.
 
E

eddie d

Bernie,

Thanks for the info .....wow, it's obvious I am way in over my head. Dont
understand the array enter. I did copy/paste the formula and it returned
#name....
 
E

eddie d

Hi Again,

Messed with it some more and got the formula to work...got the array thing
figured out...here is whats happenning, It works if I copy the formula
directly under the next and so on. The names that are referenced more than
once dont "stack" together they may be several lines below the next. It
looks something like this,

Gross Sales
Pick up
Dine In
Take out
Drive thru

Average Gross Sales
Pick up
Dine In
Take out
Drive thru

Net Sales
Pick up
Dine In
Take out
Drive thru

Average Net Sales
Pick up
Dine In
Take Out
Drive Through

Thanks for your help on this

Eddie
 
B

Bernie Deitrick

Eddie,

I am interpreting "The names that are referenced" as what we are currently using as the value from
B78 - now a list that starts in B78. If that isn't the case, then this won't work.

We can use a slightly different formula, again array entered (using Ctrl-Shift-Enter)

=INDEX($K$1:$K$384,LARGE(($I$1:$I$384=$B78)*ROW($I$1:$I$384),COUNTIF($B78:$B$100,$B78)),0)

and then copied down to match the values starting in B78.

This will find the first instance of "Pick up", then the first instance of "Dine In", etc. then the
second "Pick up", the second "Dine In", and so on, with the list as shown below in the range
$B78:$B$100. If the last value is further down the sheet than row 100, change the $100 to
$rownumber...


HTH,
Bernie
MS Excel MVP
 
E

eddie d

Hi Bernie,

again..thanks ....crazy.... it's literally a different language to
me....managed to get your first formula (all trial and error) to work by
changing a few parameters to find the first it looks like this

=IF(COUNTIF($I$1:$I$384,$B172)>=ROWS($A$1:A1),INDEX($K$1:$K$384,LARGE(($I$1:$I$384=$B172)*ROW($I$1:$I$384),COUNTIF($I$1:$I$384,$B172)-(ROWS($B$1:B1)-1))),"0.00")

to find the second occurance further down the page

=IF(COUNTIF($I$1:$I$384,$B181)>=ROWS($A$1:A$2),INDEX($K$1:$K$384,LARGE(($I$1:$I$384=$B181)*ROW($I$1:$I$384),COUNTIF($I$1:$I$384,$B181)-(ROWS($B$1:B$2)-1))),"0.00")

Here is the next one for you.. as you saw in the example

The template is set up to capture all possible data that may or may not come
through on the import.....so...when data doesnt comes through it gets tricky.
for example drive thuru sales come throught one night and not the next...so
what happens when it doesnt come through is that the data from the
technically second occuranace end up in the first occurrance position as the
there was no "first occurance" that day...hope that may sense. can we tie te
data to the heading of each group.....

Thanks again

Eddie
 
B

Bernie Deitrick

Eddie,

I need a better explanation of what your layout on the sheet is: where is the data, where is the
table that you want to pull it into, etc. Otherwise, I'm just guessing.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup to reference offset 1
VLOOKUP with Structured Reference to Table Header 4
Vlookup and return sheet name also 2
Use & with two formulas 2
Vlookup on more than one sheet 5
Vlookup with AND, OR IF statements 1
See if a range of cells contains certain text to return the value 3
vlookup returns na 7

Top