If Formula

[gadams31]

Hi, I need a formula that looks up Document number in Column L on Sheet 1, in Sheet 2 Column A, then I need it to lookup the symbol in column B next to that document number on sheet 2 and if it matches Cell AE1 on Sheet 1 to put paid  and if doesn't match put not paid on Sheet 1.  Thanks

[#2]

Hello @gadams31 

You could use Xlookup or Vlookup for that. If you have Office 365, try this XLookup formula

=IF(XLOOKUP(Sheet1!L2,Sheet2!A:A,Sheet2!B:B)=$AE$1,"paid","not paid")

If you don't have Office 365, try the Vlookup approach

=IF(IFERROR(VLOOKUP(L2,Sheet2!A:B,2,FALSE),"not found")=$AE$1,"paid","not paid")

[#3]

@Ingeborg Hawighorst thank you! That would work.  However, when I run a report on documents I get a symbol 

like this and I put that in AE1 thinking the formula would see it.  It marked everything Not Paid.  Is there any way  for a formula to recognize a symbol? I can copy that symbol not the cell.  Here is what the report looks like 

I always have to add a column and type Paid for green and Not Paid for Red and I was trying to get away from that step.  Your formula worked I didn't give enough info.  I assumed.

[#4]

@gadams31 Right. I need more information about that symbol. Is that a graphics object? Or is it a special character in the cell?

When you select a cell with the symbol, is there anything showing in the formula bar?

If this is a graphics symbol, then you can't do anything with formulas. You may want to go back to the source of the report and have the graphics symbol be replaced with text.

If it is a value in a cell, you can look at that value with Vlookup.

[#5]

@Ingeborg Hawighorst It is a graphic symbol.

Can we try this.  On Sheet 2 in column M there is a date if it is paid.  So lookup document # on sheet 1 in column L, on Sheet 2 column A and if it matches look in column M, if there is a date put Paid if it is blank put Not Paid.

Thanks for you help!

[#6]

@gadams31 Sure. You can still use Vlookup for that. The lookup value is the document number in column A and return the value in column M. If that is a number, then return "paid". 

Excel stores dates as numbers internally, so checking for a number is one of several options for the checking. 

=IF(ISNUMBER(VLOOKUP(L2,Sheet2!A:M,13,FALSE)),"paid","not paid")

And with Xlookup the formula looks like this:

=IF(ISNUMBER(XLOOKUP(L2,Sheet2!A:A,Sheet2!M:M)),"paid","not paid")

[#7]

@Ingeborg Hawighorst Thank you that worked!