IFERROR, INDEX, and MATCH Issue

Mhartdegen

New Member
Joined
May 24, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have a sheet of data that I'm attempting to pull from using the formula below.
{=IFERROR(INDEX(Verify!$A$2:$A$500,SMALL(IF(F$2=Verify!$J$2:$J$500,ROW(Verify!$J$2:$J$500)- MIN(ROW(Verify!$J$2:$J$500)),""), ROW())),"")}
I have attached a photo of my sheet. I am attempting to return all of the data from b2:b36 that match to the text in F2:Q2 to A2:A36 in an array below.
 

Attachments

  • screentshot.png
    screentshot.png
    75.7 KB · Views: 5

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi & welcome to MrExcel.
Your formula & description do not match, but based on the description try
Excel Formula:
=FILTER(B2:B100,ISNUMBER(MATCH(A2:A100,F2:Q2,0)))
 
Upvote 0
Hi & welcome to MrExcel.
Your formula & description do not match, but based on the description try
Excel Formula:
=FILTER(B2:B100,ISNUMBER(MATCH(A2:A100,F2:Q2,0)))
Maybe I can explain what I'm trying to accomplish in a better way. I am attempting to match F2:Q2 (62DACH:62EDCA) to A2:A36 and for each match to F2:Q2 return the data in B2:B36 below the F2:Q2 search data. For example, below H2 (62GAJE) the return would be B2 (2779384) and M2 (62DAKE) would return B18 (2780104), and any other matches within data.
 
Upvote 0
Did you try the formula I suggested?
 
Upvote 0
Are you using 365 as per your profile?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top