LOOKUP SEVERAL VALUES

M

mellowe

Hi All

I need to populate a cell in spreadsheet 2 based on the values of
several cells in spreadsheet 1 and not sure if VLOOKUP can help here.

On spreadsheet 1 I have bb in B2, cc in C2, dd in D2 ,ee in E2 and OO
in O2 if all of these values appear on spreadsheet 2 then I want to
populate Q2 in spreadsheet 2 with same value qq as in spreadsheet 1. I
have an array to check so it would be applied for range B2:Q200.

Please could someone advise which lookup function I can use. Thanks
 
P

Pete_UK

Try this in Q2 of sheet 2:

=IF(AND(Sheet1!B2=B2,Sheet1!C2=C2,Sheet1!D2=D2,Sheet1!E2=E2,Sheet1!O2=O2),Sheet1!Q2,0)

and copy this down to Q200. This returns 0 if the condition is not met
- you might like to change this to something else.

Hope this helps.

Pete
 
M

mellowe

This is a good formula but the only prob here is that the values may
appear in a different row e.g
In s/sheet 1 the values are in row 2 but in s/sheet 2 the values are in
row 8, so i need to check the whole array in sheet 1 against sheet 2 as
there will be new entries daily that will shift the rows in the next
days s/sheet.
So i cant just check that s/sheet1 B2 = s/sheet2 = B2
I need to check that s/sheet1 B2:B200 = s/sheet2 B2:B200

S/sheet 1 S/Sheet 2
B2 C2 D2 E2......O2...Q2 B2 C2 D2 E2......O2....Q2
bb cc dd ee OO...qq xx xx xx xx xx

B8 C8 D8
E8......O8...Q8
bb cc dd ee
OO...qq

Hope this makes sense! Any ideas how to do this? thnx
 
M

mellowe

I just answered my own question there all I had to do was put in the
arrays instead of one cell references. So thanks again for your
help!.....not sure to carry this on here but is there any way that you
can condition a cell so that if it returns a 0 you can replace this
with a drop down box for the user to select from?
 
P

Pete_UK

Data | Validation will enable you to select a value from a pre-defined
list - perhaps you can apply this to cell Q2 in sheet2. You might like
to replace the final 0 in the formula I gave you with "Please Select".

Hope this helps.

Pete
 
M

mellowe

Once again good idea!
I am aware of the Validation facility tho wasnt sure how I could
condition this on the cell Q2, I have a vaidation list set up but
couldnt see how to have a formula where if the cell was "Please Select"
then offer a drop down box with options - I was trying with the custom
box but i just cant get it to work here... any ideas on this?
 
P

Pete_UK

I see that you have a reply to your other posting about this - perhaps
the link that Debra gave you will help sort you out this time.

Pete
 
Top