Lookup entire sheet return value in column below

**aki333** · 10-18-2018, 11:20 AM

I need help to figure out how to best do this. Tried to search it and didn't see anything that works exactly. Here is what I want to do.

I want to vlookup/or hlookup into a different sheet (Bottom table is what that sheet should look like) where the value is in different column and rows and return the number underneath the lookup value.

The only way I can think of is by using combo of iferror and hlookup formula, but the formula can get very long since the actual data set I am working with is pretty large. Anyway I can do this another way?

not sure if I am explaining this correctly! Thanks for your help in advance

Capture.PNG

**Glenn Kennedy** · 10-18-2018, 11:51 AM

It's better to post an Excel sheet, rather than a non-editable picture of one... But this was a small sample:

=SUMPRODUCT(--($A$2:$C$4=G2),$A$3:$C$5)

**Glenn Kennedy** · 10-18-2018, 11:56 AM

or, alternatively:

=SUMIF($A$2:$C$4,G2,$A$3:$C$5)

**aki333** · 10-18-2018, 12:28 PM

Thank you. What if there's another line underneath the lookup value. So in this case sum wouldn't work. Please see attached excel for example.

**Glenn Kennedy** · 10-18-2018, 12:35 PM

You did not take account of the fact that the ranges are the same length, but offset by 1 (original) or 2 (new) rows:

=SUMPRODUCT(--($A$2:$C$6=G2),$A$4:$C$8)

in your last sheet.

**aki333** · 10-18-2018, 03:18 PM

Perfect, Thank yoU!!!!

**Glenn Kennedy** · 10-18-2018, 03:24 PM

You're welcome.

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Lookup entire sheet return value in column below

LinkBack

Thread Tools

Rate This Thread

Display

Lookup entire sheet return value in column below

Re: Lookup entire sheet return value in column below

Re: Lookup entire sheet return value in column below

Re: Lookup entire sheet return value in column below

Re: Lookup entire sheet return value in column below

Re: Lookup entire sheet return value in column below

Re: Lookup entire sheet return value in column below

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to lookup every row in sheet, return value from first column of same row

Lookup against two columns to return value from another column on same sheet

lookup for a cell value in entire sheet and return value from a column

[SOLVED] Lookup column in another sheet and return corresponding value

[SOLVED] Formula Needed to Return Entire Column Under Header on Second Sheet

Lookup Value in Column return entire row

[SOLVED] How do I return entire rows in a lookup of one sheet to another?

Bookmarks

Bookmarks

Posting Permissions