hello excel gurus,
I'm having issues with looking up a table and returning a value from the very first cell of the corresponding row. I tried using Index/match, but it is limited to the Column references. For example, I have the following two tables. Thanks in advance. Please help!
Table 1
Row A B C D
ABC 1 2 3 4
DEF 5 6 7 8
GHI 9 10 11 12
Table 2 Desired values
1 ABC
2 ABC
3 ABC
4 ABC
5 DEF
6 DEF
7 DEF
8 DEF
9 GHI
10 GHI
11 GHI
12 GHI
Thanks.
Hi,
Although your example looks simple it's ambiguous. Table 1 seems to have five columns. At least I'm interpreting it that way.
But you show four columns A, B, C, & D with values 1,2,3 & 4 and then seemingly a column headed "Row" in which you have the values "ABC" in A1, "DEF" in !2 ..etc.
Please clarify but uploading the actual workbook with Table 1 and manually add Table 2 so that we can understand precisely.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Sorry about that. Attached is the workbook.
Try this ARRAY formula in C8 and fill down:- MooFormula:
Please Login or Register to view this content.
* Remember - ARRAY formulas are applied using CTRL + SHIFT + ENTER, instead of just ENTER.
Thank you Moo the Dog for the answer. It works, but do you think there's a possibility to change the ($B$3:$E$5=A8) reference in the If statement to an aproximate number instead of equals to? I was thinking MRound, but could not make it work. Much appreciated!
I'm sure that complicates things... but upload a new example of what you mean and expected results... hard to read minds across the internet.
- Moo
lol true that. Please see attached. Book3.xlsx
Hi,
Since your Table 1 values seem to have no relevance to the A8:A18 values then in C8 copied down
Formula:Please Login or Register to view this content.
You could try this ARRAY formula to bring back the closest match:- MooFormula:Please Login or Register to view this content.
Well.. The Array worked superbly in the example, but did not work on my real scenario. I'm getting a #REF error. Are there any alternatives to the Array formula? I've attached a new workbook with the problem. There are 73 Columns across in Sheet 2 and 73 rows below on Sheet 1. I think I underestimated the probled. Please help!
New ARRAY formula in cell C2 on Sheet1:You can copy and paste that down to C5.Formula:Please Login or Register to view this content.
Also... looking at your new example data, your assumptions are incorrect...
ABC 310 1600 Matches E19 on Sheet2 hence pulls 1600 from Column A
- It doesn't match E19, but it is 1 off, so it does pull the 1600
DEF 191 1300 Approxes C16 on Sheet2 hence pulls 1300 from Column A
- It is closer to D17 (194) than it is C16 (205), so it pulls 1400
GHI 166 1500 Approxes D18 on Sheet2 hence pulls 1500 from Column A
- It is closer to C17 (171) than it is D18 (151), so it pulls 1400
IJK 158 2500 Approxes B18 on Sheet2 hence pulls 2500 from Column A
- It is closer to E24 (160) than it is B18 (152), so it pulls 2100
- Moo
Last edited by Moo the Dog; 03-16-2015 at 11:13 PM.
Thank you for the continuous support. Yes, the assumptions do differ. But I still get reference error when applying the formula. What am i doing wrong? Does Array formula has range limitation? I am looking over 73 Columns accross in Sheet2. Maybe that's the issue?
This is my edited formula.
=INDEX(Sheet2!$A$37:$A$67,MAX(IF(MIN(ABS(Sheet2!$B$37:$BV$67-R4))=ABS(Sheet2!$B$37:$BV$67-R4),ROW(Sheet2!$B$37:$BV$67)-2)))
How about this array formula in C2:
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Please Login or Register to view this content.
Quang PT
Thank you so much guys. Worked like a charm. Much appreciated!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Sure will. I tried to make sense of the formula, but got confused. Would you please break the formula down so that it makes sense? If you're pressed for time then please don't bother. Again, thank you for your time.
It is so hard to explain my idea on the logic hidden below the formula, especially English is not my nature language. If you are still interesting on it, send me a message then we will talk together by email, skype...
Cool. Thanks.
You can copy and paste that down to C5.
Also... looking at your new example data, your assumptions are incorrect..
Wish You A Very Happy Easter 2015
It looks like you didn't change the ROW identifier in the formula to match what your layout requires... The "-2" at the end should be changed to "-36", since your index starts in row 37, whereas the data I was working with started in row 3. That may be where the #REF error is coming from.Originally Posted by 2136gamer
...Glad to know Bebo's formula also worked.
- Moo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks