index match 1 result from 2 columns

[rjmdc]

what do i need to do to fix this formula?

=IF([@[Tabs ID]]="","",IFERROR(INDEX('Vendors with Addresses'!A:A,MATCH([@[Tabs ID]],'Vendors with Addresses'!N:N,0)),"Choose override"))
now i have 2 columns
M with payee that sometimes says choose payee and N that is the override payee
O has a W9 ( that needs to be an index match off the previous question - to look at BOTH M and N and give me the w9
Vendors with Addresses'!K:K
obviously i only want to look at results and not "Choose overrride"
is that possible?

 

[dougmarkham]

Hi rjmdc.

Please provide snapshots of your table as what you've said is very confusing.

Also, please specifically outline...
a) The field names (headers) and the context of the information
b) What are the fields you're trying to index match
Output field = header you want to pull
Match 1 field = field on the same table as the formula
Match 2 field = field on the same table as output field.
c) Worksheet names that these fields are on

Also, it's better if you do not use long-hand ranges to index match.
1) Create a table (home tab-->Format as table) so that the data is in a dynamic table
Creating a table
2) Create named ranges that spans your table and use the named range in your index match formula (that way, there is less to type and no need to put in ws names).
Creating named ranges

example
=index(outputfield,match(Table1Field,Table2field,0))

The advantage of using dynamic tables and named ranges is two fold:
1) It shortens the formula and you don't need to reference worksheets
2) If you add or subtract columns, the formula doesn't change, so you don't get yourself confused.

Kr,
Doug.

 

[rjmdc]

how do i now add this formula but to take the information from 2 columns
this was the formula
To get W9
=IFERROR(INDEX('Vendors with Addresses'!K:K,MATCH([@Payee],'Vendors with Addresses'!A:A,0)),"")

i now have vendors in columns M and N and need W9 in column O can that be done

i dint name the ranges as a letter was easier to use in my macros

 

[dougmarkham]

Hi

I'm still not quite sure I'm understanding your exact requirement tbh, as I'm having difficulty visualizing it...bear with me.

If you want the value in W9
1) set-up a macro to copy/paste W9 Range("O2") and fill down.
2) use a formula to show the text output of W9 i.e., =$W$9

If you want to show the formula in W9:
You can use: =Formulatext($W$9)

However, that only shows the formula as the value i.e., what you can see.

If you want to put the formula that is in W9 into column O, you can simply insert the formula using the macro.

Here below, you can see how I reference the dynamic table via the table name (LookUpTable) and the table header [POSTAL TOWN].
The index match formula contains named ranges but you can substitute yours into this so long as you reference the dynamic table (as I've shown beneath).

Using this method fills it down for you.

Sub IndexMatch()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual

Worksheets("InputPostCodes").Activate
Range("LookUpTable[POSTAL TOWN]").Formula = "=INDEX(PostalTown,MATCH(PostCodeFirstHalfNew,PostCode,0))"
'Range("YourTableName[YOUR HEADER FOR COL O]").Formula = "=IFERROR(INDEX('Vendors with Addresses'!K:K,MATCH([@Payee],'Vendors with Addresses'!A:A,0)),"") 
Range("LookUpTable[POSTAL TOWN]").Copy
Range("LookUpTable[POSTAL TOWN]").PasteSpecial xlPasteValues
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
    
End Sub

Btw, named ranges are easy to use in a macro, that's the reason I started using them.
There are also ways to make named ranges via macros:

Sub NameRange()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlManual
    
    Worksheets("YourSheet").Activate
    Range("YourTableName[YourHeaderName]").Select
    ActiveWorkbook.Names.Add Name:="YourNamedRangeHere", RefersToR1C1:= _
        "=YourTableName[YourHeaderName]"
        
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic
    
End Sub

After stipulating this, you can then refer to a named range in a formula you insert via a macro.