Excel Query based on data validation

[caffrey]

Is this possible in excel ? I'll try to explain it best as I can

 

I have 2 sheets

 

Sheet 1

 

 

Which through data validation col1 has a dropdown list of items from sheet 2 col1, col2 is a data validation list with "value1", "value2" in it

 

Sheet 2

 

 

What I'm trying to do is get the value from Sheet Two col2 or col3 based on the values from sheet 1 e.g.

 

sheet1 a2 (item3)+ sheet1b2(value1) then sheet1 c2 would be 1

 

if its

 

sheet1 a2 (item3)+ sheet1b2(value2) then sheet1 c2 would be 3

Edited December 1, 2020 by caffrey

[Rob_D]

If I understand you right:

You want to look up the numerical value in Sheet2 Col2 based on the item# in Col1 and present this in Sheet1 col2 based on which item# is selected in col1 (but with the word "value" in front so 1 in Sheet2 col2 becomes "value 1" in sheet 1 col2)?

In which case the following formula should do what you want (there may be better ways to do this).

 

=IFS(A2=Sheet2!$A$2,"Value "&Sheet2!$B$2,A2=Sheet2!$A$3,"Value "&Sheet2!$B$3,A2=Sheet2!$A$4,"Value "&Sheet2!$B$4,A2=Sheet2!$A$5,"Value "&Sheet2!$B$5,A2=Sheet2!$A$6,"Value "&Sheet2!$B$6)

 

All this does is an IF statement for each possible value of A2.

A2=Sheet2!$A$2 checks if cell Sheet1 Cell A2 matches Sheet 2 Cell A2 if it does then "Value "&Sheet2!$B$2 writes the string "Value " and the contents of Sheet2 Cell B2 in to the cell (so if Sheet 2 Cell B2 is 3 then it would say "Value 3").

If the check is false then it moves on to the next one which checks Sheet2 cell A3 and so on.

 

I hope this helps.

[caffrey]

Thank you,

 

It's almost there - It's difficult to explain - I'm more of a Database person and this would be easy in a query and a pair of databases

 

The lists contain :-

 

This is a data validation list from column1 sheet 2

 

 

And this is a data validation list typed manually

 

 

As per the above grabs I would like sheet 1 column 3 to equal "1", if I picked value1 then sheet1 column 3 would equal "2"

 

[Seb1780]

As I read it, the value of sheet 1 column 3 is determined by the values in column 1 and column 2 AND the value in column 2 is either 1 or 2

 

So why not use a vlookup where the col_index (third) parameter is based on what is in column 2

 

e.g. for cell C2 in your original post =vlookup(B2,sheet2!A2:C6,B2+1,FALSE)

[caffrey]

Hrrm that's not working either - It feels like it should be so easy but it's alluding me

[Seb1780]

INDEX(MATCH) then? Set the values in sheet 2 B1 and C1 to match the two possible values of the data in column B

 

Again, for your original post in cell C2

 

=INDEX(sheet2!A1:C6,match(A2,sheet2!A:A,0),match(B2,sheet2!1:1,0))

[Rob_D]

So Sheet2 has your item# (col1) and two values for that item (cols 2 and 3)

And in Sheet1 you want to be able to put in an item# and either value 1 or 2 and have it return one of the values associated with that item.

 

Seb1780's vlookup idea should do what you want. I think the only problem is that your Sheet1 uses the string "Value 1" rather than just 1 and the vlookup col_index is is looking for just a number. To fix it you just need to replace B2+1 with Right(B2,1)+1 which will just take the end character (in this case the number) of the string.

[caffrey]

Thanks for the pointers, I'll have a play and see how I get on - think I've got the gist of it now