Compound VLOOKUPS

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

This is a mulitple question on VLOOKUPS, criteria and
their tables:

1. I want a VLOOKUP to happen if a VLOOKUP result is a
certain value.

Example: If A4 & B16 are VLOOKUP statements then VLOOKUP
(Result of A4 lookup,result of B16 table name,3,False).
I guess it would look like VLOOKUP(A4,B16,3,False) but it
doesn;t work. I have tried all the "IF" statements I can
find.

2. I want a lookup to return a value based on the criteria
in a cell BUT I want the table to adapt to the criteria.
Example:

Have an input cell (call it A1)where person can chose
between "Jan" and "Feb" and in cell B1 chose the Day (1-
31).

The tables are created automatically when the first colimn
value is "whatever".

I want the VLOOKUP to shift to the called out criteria
as : VLOOKUP(A1,Cell value table, 2, False)

Month Date Sales Name
Jan 1 Bob
Jan 4 Jeff
Jan 6 Shirley
Feb 1 Janice
Feb 6 Fred
Jan 9 None

So if cell A1 = "4" & B1 = "Jan" then the Value 9
yields "none" or if A1 = 1 and B1 = "Feb" then "Janice" is
returned.

Maybe I'm asking this to do too much..........

Thanks!
 
Hi Bill
a little bit confusing but maybe the following array formula will do
what you want:
Assumptions:
- your lookup range is on a separate sheet named 'sheet2'
- A1 stores a number (the day to search for)
- B1 stores a text value (the month name to search for)

try the following formula entered as array formula (CTRL+SHIFT+ENTER)
=IF(ISERROR(INDEX('sheet2'!C1:C100,MATCH(B1&A1,'sheet2'!A1:100&'sheet2'
!B1:B100,0))),"no
match",INDEX('sheet2'!C1:C100,MATCH(B1&A1,'sheet2'!A1:100&'sheet2'!B1:B
100,0)))
 
Back
Top