VLOOKUP with TABLES

[Guest]

=IF(ISNA(VLOOKUP(C2,INDIRECT(VLOOKUP(R5:S8),2*C9))),TRUE))
The formula above requires modification.
R5:S8 is also a table named "Max_Weight_Table" which has four selections of
materials in R5:R8 and weights in S5:S8.
When C2 matches a material in column R it should multiply the corresponding
weight by C9.
Can someone help please.
Thank you in advance.
I need to be away for two hours. I will check when I come back.
Serge

 

[Biff]

Hi!

Try this:

=IF(ISNA(VLOOKUP(C2,R5:S8,2,1)),"",VLOOKUP(C2,R5:S8,2,1)*C9)

Biff

 

[Guest]

Hello Biff,
Fantastic, You did it again. It works great.
With the same formula basicaly, I want to use it in C3 for another table
with the appropriate change in the formula. But now comes another chalange.
C2 & C3 have a validation list for four different selections. If I select
data from C2 I should not be able to make a selection from C3 or visa versa.
Can something be done for this senario?
Thank you so much.
Serge

 

[Biff]

Hi!

That sounds like you'd have to configure your drop downs somehow.

I'm getting ready to call it a day so maybe someone will chime in. If not,
I'll check back here tomorrow.

Biff

 

[Guest]

Godd morning Biff,
Yes I have a validation list drop down menu with four selections for C2 &
C3. The selections are the same for both cells, But each cell look up
different tables so that's why I must select one cell or the other giving me
the results in C13. If a selection has been made in one of the cell, I need
to get a message somehow that a selection has been made in the previous cell
if by mistake I made a selection in the second cell before deleting the entry
in the previous cell.

In yesterday's VLOOKUP formula reply can you explain the function of "1" in
the formula, (what does it do?)

 

[L. Howard Kittle]

Hi Serge,

The 1 in the formula looks for an approximate match, closest but less if no
match is present. Actually, I would have thought Biff would have offered 2,
which is an exact match. 1 and True are the same and 2 and False are the
same and either may be used.

I would be glad to look at a workbook dealing with the drop downs and
vlookup if you want.

Send to (e-mail address removed)

HTH
Regards,
Howard

 

[Biff]

In yesterday's VLOOKUP formula reply can you explain the function of "1" in

the formula, (what does it do?)

In the formula you posted:

=IF(ISNA(VLOOKUP(C2,INDIRECT(VLOOKUP(R5:S8),2*C9))),TRUE))

You're using the TRUE argument in the Vlookup so I just assumed that in fact
your table is sorted. The 1 is just a "shorthand" way of using TRUE.

VLOOKUP(A1,B:C,2,TRUE)
VLOOKUP(A1,B:C,2,1)

VLOOKUP(A1,B:C,2,FALSE)
VLOOKUP(A1,B:C,2,0)

Now, about your follow-up....

I'm a little confused.

You have 2 drop downs and you want to be able to select from one but not the
other and vice versa?

How do you determine which drop down can be used if the selections are the
same for both?

I may need to see this for myself to get a better understanding. If you want
me to take a look at your file let me know how to contact you.

Biff

 

[Guest]

I can be reached at: (e-mail address removed)

In the formula you posted:


You're using the TRUE argument in the Vlookup so I just assumed that in fact
your table is sorted. The 1 is just a "shorthand" way of using TRUE.

VLOOKUP(A1,B:C,2,TRUE)
VLOOKUP(A1,B:C,2,1)

VLOOKUP(A1,B:C,2,FALSE)
VLOOKUP(A1,B:C,2,0)

Now, about your follow-up....

I'm a little confused.

You have 2 drop downs and you want to be able to select from one but not the
other and vice versa?

How do you determine which drop down can be used if the selections are the
same for both?

I may need to see this for myself to get a better understanding. If you want
me to take a look at your file let me know how to contact you.

Biff

 

[Biff]

Sent you an email.

Biff

I can be reached at: (e-mail address removed)