Return single value on multipl criteria lookup

[Guest]

Hello,

I have a spreadsheet with three columns of data (A-"treaty", B-"AY",
C-"ASL") with 247 lines of combinations of these three fields. The fourth
column (D) needs to be what I call the "AU", which will be different based on
the combination of the three data fields. Columns E-H contain financial
data. Worksheet 2 in the file lists the combinations where I can lookup the
"AU" to be input to worksheet 1, column D but I don't know how to do a look
up function that takes into account three criteria to determine one response.
Can anyone assist with this? I would greatly appreciate it.

Thanks.
Deborah

 

[Bernard Liengme]

Please condense the question to its simplest form - omit unneeded info (like
column e-h!)
Give an example of a typical row (not LINE)
Tell how the table if set out.
best wishes

 

[Guest]

why not combine the three cols into a new col and lookup the resulting string

 

[Dave Peterson]

One way:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))