=if(isblank .... ) Circular Error

[jkhania]

I am using the if formula in in addition to the isblank formula to have Excel pull serial numbers/locations from a database list.

I currently have it set that one column requires me to enter in say the location and then the second column uses the if formula to initiate the MATCH formula to pull the serial number out of my database list.

I want to have it so I can enter either the serial number OR the location in their respective columns and have the corresponding column pull the information in the database list.

Example:

 

[C Moore]

You would have to do it in 3 columns to avoid using VBA AND keep formulas in the cell at all times. The new column would be the input and the other two searches.

You could use the name manager for a simple name, say Result, with a complicated formula. You'd enter either term you wanted and =Result in the other.

 

[jkhania]

I get the three column approach but could you show an example of the =result / Name Manager functions?

 

[C Moore]

I think the name manager is the most under utilized aspects of Excel. I'll try and lead you through it. Let's do 3 names, Search4Serials, Search4Locations, and Result.

With A8 selected, new name, Search4Serials refers to:=Index('[New Devices at HQ.xlsx]HQ Devices'!$E$E,MATCH(A8,'[New Devices at HQ.xlsx]HQ Devices'!$A$A,0)), please note have A8 selected when you create the name and A8 in refers to does not have $$.
With B8 selected, new name, Search4Locations refers to:=Index('[New Devices at HQ.xlsx]HQ Devices'!$A$A,MATCH(B8,'[New Devices at HQ.xlsx]HQ Devices'!$E$E,0)), please note have B8 selected when you create the name and B8 in refers to does not have $$.
Result refers to: =if(column()=1,Search4Locations,Search4Serials)

Now enter either value you have, and in its partner cell type =Result (or '=res' then TAB). So Result will use the Index-Match you created for it based on which column it is in.