Dec 05 2020 11:34 PM - edited Dec 06 2020 12:38 PM
Dec 05 2020 11:34 PM - edited Dec 06 2020 12:38 PM
Hello there,
I have a large file of data and I have a set of random values from a different excel workbook that I am trying to match to my large file. For example, lets say I have four columns A,B, C, & D in my large file. I am trying to match the random values I have from the other excel workbook to column B, If an exact match was found, I want to return the values from columns A and D along with column B. However, I am having trouble with writing this formula. so the new formatting would return in a table and look like column B first then Column A, then Column D in a table.
Thank you,
Dec 06 2020 12:52 AM
Dec 06 2020 09:36 AM
@Deleted
Perhaps you mean something like
=INDEX(A:D,MATCH(value,B:B,0),{1,2,4})
Dec 06 2020 10:39 AM
Thank you for the replies. Please see the attached screenshot for reference.
Dec 06 2020 10:40 AM
Dec 06 2020 10:58 AM
@Deleted
In theory it shall work, at least on Excel with dynamic arrays.
Dec 07 2020 12:04 AM - edited Dec 11 2020 09:09 PM
@Deleted
=INDEX([Workbook.xls]Sheet1!$F$2:$F$10,MATCH(A2,[Workbook.xls]Sheet1!$b$2:$b$10,0))
You get related data in column C .
Note:
Dec 07 2020 03:54 AM
That returns only one row and all columns. Initial formula returns all rows and two columns at once.
Dec 07 2020 11:56 PM - edited Dec 07 2020 11:58 PM
No this returns match value from one column only, since Index is on Column F.
=INDEX([Workbook.xls]Sheet1!$F$2:$F$10,MATCH(A2,[Workbook.xls]Sheet1!$b$2:$b$10,0))
To get values from multiple columns this needs to modified:
=INDEX([Workbook.xls]Sheet1!$A$2:$F$10,MATCH(A2,[Workbook.xls]Sheet1!$b$2:$b$10,0),{1,2,4})
For multiple Rows an Array (CSE) formula is required with SMALL function.
{=INDEX($A$1:$D$25, SMALL(IF(COUNTIF($G$1, $B$1:$B$25), ROW($A$1:$D$25)-MIN(ROW($A$1:$D$25))+1), ROW(A1)), COLUMN(A1))}
Dec 08 2020 03:54 AM
That's what in initial formula, it returns columns {1,6} and it's not clear why second one doesn't work. Multiple rows in initial formula are for dynamic array case as MATCH(range1, reange2, 0), but in this case that's most probably doesn't matter.
Dec 09 2020 12:52 AM
Considering the last post my 1st formula returns value form column F only. The second one returns from column A, B, and D only,, the last one returns multiple records if match with the criteria, and all are working !
Dec 09 2020 01:39 AM
I have no doubt it works in your case, the question is why it doesn't work for @Deleted
Dec 11 2020 09:08 PM
Check the Note part of my first post, I've shown the reason why OP's formula is not working!
Dec 12 2020 02:41 AM
MATCH(A2:A10,.. shall work. On Excel with dynamic arrays it returns an array, on old Excel implicit intersection shall be silently switched on and single value will be taken.
Dec 13 2020 08:23 PM