VLOOKUP Multiple searches one answer

  • Thread starter Thread starter edrachel
  • Start date Start date
E

edrachel

I am trying to get the speadsheet to look at different items and if no
all fields are found with a value I still it to return a value from
designated Cell. Hope this makes sense.


B7 = MD on worksheet named “Products”
B8 = 001 on worksheet named “products”
A1 = MD on worksheet named “Rates”
B1 = 001 on worksheet named “Rates”
C1 = 30 on worksheet Named “rates”

MD 001 30

Formula:

=VLOOKUP(B7,RATES!A1:C250,2,FALSE)

This will pull up the value “001”, I want to pull the value “30”

I want to be able to tell VLOOKUP to look for the MD and the 001 an
give me the value next to the 001.

I also want to be able to pull the “30” if I have a value in B8 in th
Products w/s that can’t be found after matching with the MD(B7).
Example: If I put 004 in the B8 cell and there was no correspondin
004 in the Rates worksheet next to MD, assuming that the B1 cell i
blank, I would get the value of the next cell which would again be m
30.

I hope hope I didn't confuse everyone. Your help will be greatl
appreciated
 
For the first part of your question you could insert a new column on the rate sheet between B and C. Then in this new column enter the formula:

=A1&B1

and fill down.

Now you can use the VLOOKUP on the concatenated value.

=VLOOKUP(B7&B8,RATES!C1:D250,2,FALSE)

For the second part you could use an IF statement to see if the concatenation is found, and if it isn't do a VLOOKUP on only the first component only:

=IF(ISNA(VLOOKUP(B7&B8,RATES!C1:D250,2,FALSE)),VLOOKUP(B7,RATES!A1:D250,4,FALSE),VLOOKUP(B7&B8,RATES!C1:D250,2,FALSE))

You would use this formula instead of the first one. Basically the IF checks if the concatenated value can't be found. If it isn't then it looks only for the first component, If it is then it returns on the concatenation.

If you have any problems I will check this thread on Monday.

Good Luck,
Mark Graesser
(e-mail address removed)


----- edrachel > wrote: -----

I am trying to get the speadsheet to look at different items and if not
all fields are found with a value I still it to return a value from a
designated Cell. Hope this makes sense.


B7 = MD on worksheet named “Productsâ€
B8 = 001 on worksheet named “productsâ€
A1 = MD on worksheet named “Ratesâ€
B1 = 001 on worksheet named “Ratesâ€
C1 = 30 on worksheet Named “ratesâ€

MD 001 30

Formula:

=VLOOKUP(B7,RATES!A1:C250,2,FALSE)

This will pull up the value “001â€, I want to pull the value “30â€

I want to be able to tell VLOOKUP to look for the MD and the 001 and
give me the value next to the 001.

I also want to be able to pull the “30†if I have a value in B8 in the
Products w/s that can’t be found after matching with the MD(B7).
Example: If I put 004 in the B8 cell and there was no corresponding
004 in the Rates worksheet next to MD, assuming that the B1 cell is
blank, I would get the value of the next cell which would again be my
30.

I hope hope I didn't confuse everyone. Your help will be greatly
appreciated.
 
Back
Top