LinkBack URL
About LinkBacks
I have an excel sheet with lots of rows separated in different ranges, i want to lookup "B" and return the value of the cell to its left, the table array should be based on a range that is defind in cells in column A.
see example
Capture.JPG
I have an excel sheet with lots of rows separated in different ranges, i want to lookup "B" and return the value of the cell to its left, the table array should be based on a range that is defind in cells in column A.
see example
Capture.JPG
Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired results are also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
if the "Range" is real case,
it need two helper column,
One to identify the range,
in column D,
in first row, put 1
second row onward, put this formula
Formula:
Please Login or Register to view this content.
Another is to create unique ID
put this formula in E and drag down.
Formula:Please Login or Register to view this content.
then create a index and match function to get the result.
Thanks for the reply.
As you advised I attached a sample worksheet.
The desired results are in cells M6:P7.
I color coded the desired results and the table where the values are to show only where the value for the specific result shuold be returned from.
In M6, the below uses a dynamic named range:
If you don't want the named range, then replace it with: $A$29:$J$84
Please Login or Register to view this content.
----------------
the named range is:
Sorry, I posted the wrong workbook...Please Login or Register to view this content.
Last edited by protonLeah; 04-18-2019 at 04:17 PM. Reason: posted wrong workbook :(
Ben Van Johnson
Thanks for the reply, It works for the first result I need, however when I copied the formula to the next cell and changed the MATCH lookup value to "Returns Date" the value returned was the same as the "Invoice" return, I also tried changing the number in the SMALL function to 2, but then it returned an error.
See attached.
Here's the correct workbook. It's slow (using whole columns) I don' t have time to fix that part:
Last edited by protonLeah; 04-18-2019 at 04:22 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks