Forum Discussion
Can you use AND / OR in an INDEX MATCH
Hi I have am array formula that looks like this: =INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2),0),1) which works. I want to add...
ricardo2260 I want to retrieve data in these 2 tables with options, search in table A, if there is, retrieve column data, if there is none, search in table B, if found in column B, retrieve the column.
so I see you are actually using google sheets and not excel so i don't even have to ask what version, which is good because you should use some newer functions. if I'm reading your formula right try:
=LET(first, XLOOKUP(B20, 'NM & CRM'!$D$4:$D, 'NM & CRM'!$C$4:$C,""), IF(first<>"",first, XLOOKUP(B20, 'NM & CRM'!$I$4:$I, 'NM & CRM'!$H$4:$H,""))
basically do the first lookup and assign it to 'first' then check if a value was found and if not do the second lookup.
=LET(first, XLOOKUP(B20, 'NM & CRM'!$D$4:$D, 'NM & CRM'!$C$4:$C,""), IF(first<>"",first, XLOOKUP(B20, 'NM & CRM'!$I$4:$I, 'NM & CRM'!$H$4:$H,""))
basically do the first lookup and assign it to 'first' then check if a value was found and if not do the second lookup.
- I can do it, thanks for the help mtarler