Lookup value from different lists and return different results

[jackson_hollon]

Hello,

Thanks for looking at my thread. I have attached a file here. I have the result on column R to T. I have 3 lookup lists on column A, C, E.

If column Rx = column A, then lookup value from column H to I, value 2, then

If column Rx = column C, then lookup value from column K to L, value 2, then

If column Sx = column E, then lookup value from column N to O, value 2

I hope I explain it well enough.

Thanks for the help.

Jackson

[leelnich]

Hi Jackson- Questions:
1) What is meant by "value 2"? The second data row within the relevant column?
2) Where do you want the formula? Column T, row by row?
3) Rx,Sx - is x a variable row? How does that work?

[jackson_hollon]

leelnich,

Q1) The value 2 = value from second column . Example: from column H to I , data from column I
Q2) The formula should be in column T. These are the result I want
Q3) Rx = R column and x = row. For example= R1, R2, R3....

Thanks for the help.

[leelnich]

In T2 and copy down:
=IFERROR(VLOOKUP(R2, H:I, 2, 0),0)+IFERROR(VLOOKUP(R2, K:L, 2, 0),0)+IFERROR(VLOOKUP(S2, N:O, 2, 0),0)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[chullan88]

Not the most elegant solution..

Im sure this can be done much easier using MMULT..not familiar with that function ..

Value in T2 = =INDIRECT(ADDRESS(SUM(N(($H$1:$O$12=$R2)*ROW($H$1:$O$12))),SUM(N($H$1:$O$12=$R2)*COLUMN($H$1:$O$12))+1))

To be array entered ( Ctrl+Shift+Enter)

Please Login or Register  to view this content.

[FlameRetired]

Still not sure I understand. This returns the values the upload indicates. It must be array entered in T2 and filled down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[jackson_hollon]

leelnich,

Sorry. It should be only one

[FlameRetired]

@ LLN
Yes there is one AS 10 but two AS10 ... My formula only returns the first number in column I ... 40 and does it twice. Same as upload.

[jackson_hollon]

chullan88,

Thanks for the reply. The formula works on the list for column H and K, but doesn't work for N. The column T works from row 2 to 15, but not work on row 16, 17 and 18.

Thanks for the help.

Jackson

[leelnich]

Actually, they differ. One has a trailing space, but none of the Rx match it. Didn't really matter, this works:
In T2 and copy down:
=IFERROR(VLOOKUP(R2, H:I, 2, 0),0)+IFERROR(VLOOKUP(R2, K:L, 2, 0),0)+IFERROR(VLOOKUP(S2, N:O, 2, 0),0)

(Also shown in post #4, post-script)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[jackson_hollon]

Dave,

Your formula works perfectly.

Thanks for the help.

Jackson

[chullan88]

Sorry, missed that part

In that case, the formula would stretch to

Please Login or Register  to view this content.

Array Entered

FlameRetired's solution is much better...

[chullan88]

Actually, they differ. One has a trailing space, but none of the Rx match it. Didn't really matter, this works:
In T2 and copy down:
=IFERROR(VLOOKUP(R2, H:I, 2, 0),0)+IFERROR(VLOOKUP(R2, K:L, 2, 0),0)+IFERROR(VLOOKUP(S2, N:O, 2, 0),0)

(Also shown in post #4, post-script)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Excellent solution mate! It was so simple

[FlameRetired]

Actually, they differ. One has a trailing space, but none of the Rx match it.

Another good catch!

[FlameRetired]

@ Jackson
You are welcome. Thanks for the feedback.

[leelnich]

Thank you all, happy to help!

[jackson_hollon]

chullan88,

Thanks for the help.

Jackson

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.