Multiple VLookup Values

[alexfflores]

Hello All,

I am wondering if someone can help me with a custom Macro using the
VLOOKUP logic.

I need to do something like:

VLOOKUP(lookup_value1,lookup_value2,table_array,col_index_num,range_lookup)

Where lookup_value1 AND lookup_value2 must be there for the item in
col_index to be returned.

So I guess I would need to let the macro which column lookup_value1 and
lookup_value2 should test in?

Does this make sense?

Alex

 

[Dave Peterson]

Yep. But you don't even need a macro to do this...

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

(still an array formula)

 

[alexfflores]

Hi Dave,

Thanks for the suggestion.

Here's what the sheet data looks like that I am trying to get the
information from...

1/30/2006 16:30 9 1
1/30/2006 17:00 11 1
1/30/2006 17:30 12 5
1/30/2006 18:00 9 2
1/30/2006 18:30 0 0
1/31/2006 6:00 0 0
1/31/2006 6:30 1 0
1/31/2006 7:00 5 1
1/31/2006 7:30 9 3
1/31/2006 8:00 10 1
1/31/2006 8:30 13 0
1/31/2006 9:00 10 1
1/31/2006 9:30 14 1
1/31/2006 10:00 12 1


I'd like to look up the date value but also lookup the time value and
only return the value in the 3rd column if both the time and date
match. The above items are just a sample of what I have, the sheet
lists more days and repeats the times in each day.

So: if date matches 01/31/2006 and time matches 7:00 the value in
column 3 is returned of 5.

I guess I need to better understand the INDEX and MATCH formulas. I
tried to enter this into Excel but don't quite understand why it's not
working.

I'll try to breakout the formulas to see if it makes more sense.

Alex

 

[alexfflores]

Hi Dave,

I've got it! Your formula was perfect!

Thanks,
Alex

 

[alexfflores]

One strange thing:

All work fine except for 11:30 AM.

Any thoughts why?

 

[Bob Phillips]

This often happens with time, it is due to arithmetic precision.

Try this alternative

=INDEX($C$1:$C$100,MATCH(1,(J1=$A$1:$A$100)*(TEXT(J2,"hh:mm")=TEXT($B$1:$B$1
00,"hh:mm")),0))

or even

=INDEX($C$1:$C$100,MATCH(1,(J1=$A$1:$A$100)*(TEXT(J2,"hh:mm")=TEXT($B$1:$B$1
00,"hh:mm")),0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Chris Berry]

I've tried to apply this to some work I'm doing and I get #NA. This i
my data:

SSN Name Source Amount
999999999 John Doe 1 356.35

This is the formula I'm using:

{=INDEX(Sheet1!D11253,
MATCH(1,(A5=Sheet1!A11253)*(C5=1),0))}

Any help is greatly appreciated

 

[Chris Berry]

I've tried to apply this to some work I'm doing and I get #NA. This i
my data:

SSN Name Source Amount
999999999 John Doe 1 356.35

This is the formula I'm using:

{=INDEX(Sheet1!D11253,
MATCH(1,(A5=Sheet1!A1:A1253)*(C5=1),0))}

Any help is greatly appreciated

 

[Chris Berry]

Is there a trick to getting ISNA to work with array formulas? This i
what I have.

{=if(isna(INDEX(Sheet1!$D$1:$D$1253,
MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0))))

 

[Ardus Petus]

Try:
{=INDEX(Sheet1!D11253,
MATCH(1,(A5=Sheet1!A1:A1253)*(C5=1),0))}

HTH

 

[Chris Berry]

Thanks I got it to work with:

{=INDEX(Sheet1!$D$1:$D$1253,
MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0))}

Is there a trick to getting ISNA to work with Array Formulas?

=if(isna(INDEX(Sheet1!$D$1:$D$1253,
MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0)),INDEX(Sheet1!$D$1:$D$1253,
MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0)))

 

[Dave Peterson]

There isn't any trick, but you don't need to do that much work.

You would only have to check to see if there was a match:

=if(iserror(match(1,(...)*(...),0)),"Not found",
index(...,match(1,(...)*(...),0))

It'll make the formula just a bit smaller.

Another option would be to use multiple cells. Put the formula in one, then
put:

=if(iserror(x99),"Not Found",x99)

You could hide that intermediate column that contains the "real" formula.